Jump to content

How to check if data already exists in SQL


Extinction

Recommended Posts

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)

 

Link to comment

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 by Extinction
Link to comment
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 by Ayush Rathore
Link to comment

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.

  • Like 2
Link to comment

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)

 

Link to comment

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 ]]

 

Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...