mysql_fetch_assoc - db... Alternative ?

[Karuzo]

Hey Guys,

So i have a mysql handler, but since mysql_....._.... functions need the mysql module , i want to use the db... functions,

but what's the alternative to mysql_fetch_assoc ?

lets say i have a query ,

datas =dbQuery(.....) 

And what does mysql_fetch_assoc do ?

if i have a line like that:

local rowss= mysql_fetch_assoc ( datas ) 
  

[Castillo]

Returns an associative table containing the current row of the last executed query. You can call this function repeatedly to retreive all the result rows. When there aren't more rows in the result it returns nil.

You can use dbQuery + dbPoll to do this.

[Karuzo]

And how exactly ?

[Castillo]

What exactly are you trying to do?

[Karuzo]

trying to make a function to simply check some datas .

I've found a small help, but the functions which were used were mysql_.. functions, and i don't want to use the module.

So im trying to make it with db functions.

  
function mysqlCompare ( Tabelle, Spalte, Where ) 
local executer = dbConnect( "mysql", "dbname=***;host=****", "***", "***") 
    local daten = dbQuery( executer, "SELECT "..Spalte.." FROM "..Tabelle.." WHERE "..Where ) 
    local rows= dbPoll(daten,-1) 
    if daten then 
        if ( rows >= 1 ) then 
            local zeilen = mysql_fetch_assoc( daten,-1 ) 
            dbFree ( daten ) 
            return zeilen[Spalte] 
        else 
            dbFree ( daten ) 
            return false 
        end 
    end 
end 

[Castillo]

Connecting to the database everytime isn't good, just connect once and use the connection handler.

[Karuzo]

Oh yeah, thank you.

But what about that mysql_fetch_assoc?

[Castillo]

I don't understand the function you posted, if you want to compare a data, then you must give something to compare it against.

[Karuzo]

Yeah doing that in the login script.

  
 function login_func ( username, password ) 
if tostring ( mysqlCompare ( "wcf1_user", "username", "username = '" ..tostring ( username ).."'" ) ) then 
            local salt = tostring ( mysqlCompare ( "wcf1_user", "salt", "username = '"..tostring ( username ).."'" ) ) 
            local pass = tostring ( mysqlCompare ( "wcf1_user", "password", "username = '"..tostring ( username ).."'" ) ) 
            local password = sha1 ( salt..sha1 ( salt..sha1 ( password ) ) ) 
    if pass == password then 
  
--...... 

[Castillo]

You are making a lot of queries just for the login? that's highly inefficient.

[Karuzo]

Hmm, what would you suggest me to do then ?

[Castillo]

Well, why do you need to make that much queries? a single query should do.

[Citizen]

Hummmm KRZO ....

local qh = dbQuery(connect, "SELECT * FROM accounts WHERE username='?' AND password='?'", username, password) 
local result = dbPoll( qh, -1 ) 
if result and #result == 1 then 
    --Good login and password since the query returned a result 
    local datas = result[1] 
    --datas is a table with all columns of the accounts table (but only with the datas of the player) 
    -- datas.username contains the username in the database 
else 
    -- Wrong login and/or password 
end 

Taken from viewtopic.php?f=91&t=72059#p667863

Thought you got it and that it was "more clear":

But thank you for your reply, now it's more clear

And dbPoll is already doing the mysql_fetch_assoc for you since it returns the result as a table like mysql_fetch_assoc does.

[Karuzo]

You mean the

  
pass == password 
  

?

//Edit:

Idk what the guy which wrote that code , means with the zeilen = ...

i mean what returns the index zeilen[...] ?

[Karuzo]

Ok, forget about that function, k ?

I want to check if the player name which i SELECT from the database is like the username which i get triggered, and than get the salt of that acc, and the salted password, and than compare it with the password which i get triggered either.

But how can i "pack" that in one Query, as many Queries wouldn't be that good, right ?

[Wei]

local sha = 000 -- your sha code here 
query = dbQuery(con, "SELECT * FROM accounts WHERE username = '"..username.."'") 
result = dbPoll(query, -1) 
    if (#result ~= 0) then 
        if (result[1].password == sha) then 
            -- password and username matching 
        else 
            -- wrong password 
        end 
    else 
        -- username not found 
    end 
dbFree ( query ) 
     

If you are using module mysql functions, then ignore this

[Karuzo]

Yeah, thank you that's helping me a lot.

Tried it but it's not workin.

I have a function which i can use to "sha" it, for example sha1(msg).

But that's not workin, it gives me "Password isn't correct."

function login_func ( username, password ) 
local sha = sha1(password) 
local query = dbQuery(connect, "SELECT * FROM wcf1_user WHERE username = '"..username.."'") 
local result = dbPoll(query, -1) 
    if (#result ~= 0) then 
        if (result[1].password == sha) then 
                local x, y, z = data[1].x or 0, data[1].y or 0, data[1].z or 0 
                local money = data[1].money or 0 
                local skin = data[1].skin or 0 
                local health = data[1].health or 100 
                local int = data[1].int or 0 
                local dim = data[1].dim or 0 
                setElementData(source, "LoggedIN",true) 
                outputChatBox("Du hast dich erfolgreich eingeloggt!",source,0,125,0) 
                outputChatBox("Willkommen zurück,  "..string.gsub ( getPlayerName ( source ), '#%x%x%x%x%x%x', '' ),source,0,125,0) 
                triggerClientEvent(source,"closeLoginPanel",source) 
                setPlayerMoney(source, money) 
                setElementModel(source, skin) 
                setElementHealth(source, health) 
                setElementInterior(source, int) 
                setElementDimension(source, dim) 
                setElementPosition(source, x, y, z) 
                setElementData ( source, "username", username ) 
        else 
            outputChatBox("Passwort isn't correct", source,125,0,0) 
        end 
    else 
        outputChatBox("Irgendetwas stimmt nicht.", source, 125,0,0) 
    end 
end 
addEvent("LoginRequest",true) 
addEventHandler("LoginRequest", root, login_func) 
  

[Citizen]

Ok one more (and hope the last) time:

local qh = dbQuery(connect, "SELECT * FROM accounts WHERE username='?' AND password='?'", username, password) 
local result = dbPoll( qh, -1 ) 
if result and #result == 1 then 
    --Good login and password since the query returned a result 
    local datas = result[1] 
    --datas is a table with all columns of the accounts table (but only with the datas of the player) 
    -- datas.username contains the username in the database 
else 
    -- Wrong login and/or password 
end 

Requirement: When you are doing the INSERT query to register the player account, make sure you put the hashed password in that query.

EDIT: @Wei: it's a bad practice to get compare the password in the script when you can just let the SQL server do it for you. With my query, it won't get and return the useless datas if the username and password doesn't match.

When using this script (to check if the login/password are good), you have to give the query the username and the hashed password.

Here is what the query means (i.e, the username is "KRZO" and the hashed password is "abcd"):

- Please give me the value of all columns of all the lines from the table accounts that have KRZO in the column username and that also have abcd in the password column.

So normally, there is only one line in the table that has KRZO in the username column. But now imagine the hashed password is "wxyz", the sql server will do something like:

- Well, I found 1 line in the table with the username KRZO but the hashed password isn't wxyz so no, there is no result for your query man.

Can't explain the query better than this, so I hope you will understand.