Account system with MySQL

[TheCapn]

Hello guys,

At the player's login, I would like to know if his username is registred in the database. If it's registred, I would like to gather all the information of the account and put it on several variables.

However, I quite impressed by the mySQL in LUA so I have difficulties to make request.

Could you give me an exemple of the system I've just described ?

Regards,

[novo]

There you go: (Please, note you must add some kind of security checks in order to avoid MySQL injections.).

  
local connection = dbConnect("mysql", "dbname=testdb;host=127.0.0.1", "username", "password") 
if not connection then 
    outputChatBox("Could not connect"); 
end 
-- 
function loginPlayer (player, username, password) 
    local query = dbQuery(connection, "SELECT * FROM users WHERE username = '"..username.."' AND password = '"..password.."'"); 
    local result, num_affected_rows, last_insert_id = dbPoll(query, -1); 
    if num_affected_rows >= 1 then 
        for i,v in pairs(result) do 
            -- MySQL stored account data 
        end 
    else 
        outputChatBox("Wrong username or password"); 
    end 
end 
  

[TheCapn]

Thanks a lot ! I'm going to try this as soon as possible

[novo]

I also recommend you to use md5 for passwords and be sure you add security checks.

[WhoAmI]

For better safety you can generate salt, as IPB does. Random salt and the code would be

md5( md5(salt) .. md5(password) ) 

That is the strongest hash.

[Wei]

You can also use sha512 encryption with mabako sha module, or intergrated sha256 function...

[TheCapn]

Thanks,

But now how can I get all the values of my table ?

[Wei]

"SELECT * FROM users" 

[WhoAmI]

Then you use dbPoll.

For example

local query = dbQuery(handler, "SELECT * FROM users") 
local data = dbPoll(query, -1) 
for k,v in ipairs(data) do 
    outputChatBox(v["column1"]) 
end 

It outputs all values saved in 'column1'.

@OFFTOPIC: Wei

'Diet with russian vodka, lose 3 days in one week !' - Polish is the best one.

[TheCapn]

Thanks !