How to check if data already exists in SQL

[Extinction]

I'm working on a MySQL based login, and I am making my register function, I want to check if the username already exists in the database - 

 

function register(username, passowrd)
if username ~= nil and password ~= nil then
local query = dbQuery(database, "SELECT * FROM accounts WHERE username = ? AND password = ?", username, password)
-- How do I check if the username already exists 


   end
end
addEvent("register", true)
addEventHandler("register", root, register)

 

[Mr.Loki]

count the result of the query if > 0 then there's an account

Edited March 23, 2017 by Mr.Loki

[Extinction]

So like this -

 

function register(username, passowrd)
if username ~= nil and password ~= nil then
local query = dbQuery(database, "SELECT * FROM accounts WHERE username = ? AND password = ?", username, password)
   if query > 0 then 
      outputChatBox("Account exists", source)
   else 
      
      -----

   end
end
addEvent("register", true)
addEventHandler("register", root, register)

 

Edited March 23, 2017 by Extinction

[Mr.Loki]

You gotta use dbPoll to make sure that the query is completed then use the result of the poll. if > 0 then acc exists

Edited March 23, 2017 by Mr.Loki

[Ayush Rathore]

2 hours ago, Extinction said:

I'm working on a MySQL based login, and I am making my register function, I want to check if the username already exists in the database - 

 

function register(username, passowrd)
if username ~= nil and password ~= nil then
local query = dbQuery(database, "SELECT * FROM accounts WHERE username = ? AND password = ?", username, password)
-- How do I check if the username already exists 


   end
end
addEvent("register", true)
addEventHandler("register", root, register)

 

use this

function register(username, passowrd)
if client then
if username ~= nil and password ~= nil then
local query = dbQuery(database, "SELECT * FROM accounts WHERE username = ? AND password = ?", username, password)
local result = dbPoll(query,-1)    
   if #result > 0 then 
      outputChatBox("Account exists", client)
   else 
      outputChatBox("Account not exists", client)
   end
end
end
end
addEvent("register", true)
addEventHandler("register", getRootElement, register)

and for security reasons use client not source

Edited March 23, 2017 by Ayush Rathore

[3aGl3]

You should check the username only.
It would also be possible to simply flag the username column as unique and not check it at all. In that case the query will simply fail and just telling the user that it didn't work is sufficient enough.

[Extinction]

So like this -

function register(usernamem, password)
if client then
if username ~= nil and password ~= nil then
local query = dbQuery(database, "SELECT * FROM accounts WHERE username = ?", username)
local result = dbPoll(query,-1)    
   if #result > 0 then 
      outputChatBox("Account exists", client)
   else 
      outputChatBox("Account not exists", client)
   end
end
end
end
addEvent("register", true)
addEventHandler("register", getRootElement, register)

 

[NeXuS™]

Right, like that @Extinction.

[Extinction]

So how would I make the player login, or check if their login credentials are valid?

[3aGl3]

Something like this would do the trick.
Note that databaseQuery is a wrapper function I wrote for dbQuery and dbPoll, also make sure to use the same password hashing you do when registering, if you don't have one yet feel free to use the method I use, otherwise the login will always be invalid.

 

--[[
- Attempts to log the player into the given account
-
-	@param <string> username: Username for the account
-	@param <string> password: Password for the account
]]
function requestPlayerLogin( username, password )
	if not username or not password or username == "" or password == "" then
		outputDebugString( "Function requestPlayerLogin called without an username or password.", 2 )
		return
	end

	-- get the account from the database
	local result = databaseQuery( "account", "SELECT `account_id`, `name`, `password`, `online` FROM `accounts` WHERE `name`=?", username )
	if result then
		-- grab the account data from the query result
		local account = result[1]
		
		-- get the salt from the password field and hash the password send by the client
		local salt = string.sub( account.password, 65 )
		password = sha256( salt..password )
		
		-- check if the hash and the database hash match
		if password == string.sub( account.password, 1, 64 ) then
			-- check the online state of the account
			if account.online == 1 then
				-- let the player know that his account is already logged in
				outputChatBox( string.format( loc(client, "your_account_is_already_logged_in"), get("website") ), client, 255, 128, 128 )
				return
			else
				-- set the accounts online flag
				result = databaseQuery( "account", "UPDATE `accounts` SET `online`=1, `last_online`=CURRENT_TIMESTAMP WHERE (`account_id`='?')", account.account_id )
				if result then
					outputDebugString( "Successfully logged player ".. getPlayerName(client) .." in." )
					
					-- save the players account id
					playerAccount[client] = account.account_id
					playerAccountName[client] = account.name
					
					-- trigger the server and client login event
					triggerEvent( "onPlayerSQLLogin", resourceRoot, client, account_id )
					triggerClientEvent( client, "onClientPlayerLogin", resourceRoot )
					
					-- log a successfull login
					databaseQuery( "account", "INSERT INTO `accountlogins` (`account`, `address`, `serial`, `success`) VALUES (?,?,?,?)", account.account_id, getPlayerIP(client), getPlayerSerial(client), 1 )
					return
				end
			end
		end
		
		-- log a failed login attempt
		databaseQuery( "account", "INSERT INTO `accountlogins` (`account`, `address`, `serial`, `success`) VALUES (?,?,?,?)", account.account_id, getPlayerIP(client), getPlayerSerial(client), 0 )
	end

	-- if we reach this the login request failed

	-- NOTE
	-- We stick to a generic error message, even though this isn't super user friendly
	-- this prevents a hacker from finding out valid usernames by trying to log into them.
	outputChatBox( loc(client, "invalid_username_or_password"), client, 255, 128, 128 )
end		--[[ requestPlayerLogin ]]