Jump to content

SQL Insert question


kevin433

Recommended Posts

Hi,

I'm currently trying to make an account system with SQL. I have already made a login and register command and it works also. Now I want to make a command where the player types /savespawn and his spawn coordinates are getting changed in his "profile" in the SQL table. Now, my problem is how can I select the players "colum" i the database and change his coordinates. My table is setup like this:

"Name", "Password", "SkinID", "Geld", "SpawnX","SpawnY","SpawnZ"

How can I insert values into SpawnX, Y and Z without "re-setting" Name, Password, SkinID and Geld?

Link to comment

for SQLite (haven't tested though):

function saveSpawn(thePlayer)
local x, y, z = getElementPosition(thePlayer)
if executeSQLUpdate("yourTableName", "SpawnX = '" .. x .. "', SpawnY = '" .. y .. "', SpawnZ = '" .. z .. "'", "Name = '" .. getPlayerName(thePlayer) .. "'") then
outputChatBox("Spawn coordinates saved", thePlayer)
else
outputChatBox("Erorr blablablah", thePlayer)
end
end

change "yourTableName" to.. well, your table name :D

Link to comment

INSERT adds a new row to the table

UPDATE updates already existing row

you should probably read about basic SQL syntax :P

also, you can use executeSQLQuery, it'll be something like this:

executeSQLQuery("UPDATE yourTableName SET SpawnX = '" .. x .. "', SpawnY = '" .. y .. "', SpawnZ = '" .. z .. "' WHERE Name = '" .. getPlayerName(thePlayer) .. "'")

Link to comment

It isn't the best to do queries like ... WHERE `name` = '...' because that will get slow if you have a slightly bigger database. It would be the best to add a index for the name (ADD INDEX(`name`)). Even better would be to make your table id-driven - that's infact the best solution so you literally ADD PRIMARY KEY(`id`) and make it AUTO_INCREMENT. If not using phpMyAdming then ALTER TABLE sql-command will help you to make it AUTO_INCREMENT.

Then you would literally load the `id` into a variable for the player. Then you can easily run queries optimized and faster, like this

-- where playerID is the database ID for the player
executeSQLUpdate("yourTableName", "`SpawnX` = '" .. x .. "', `SpawnY` = '" .. y .. "', `SpawnZ` = '" .. z .. "'", "`id` = '" .. playerID .. "'")

That would make it

UPDATE `yourTableName` SET `SpawnX` = '.. x ..', `SpawnY` = ' .. y .. ', `SpawnZ` = ' .. z .. ' WHERE `id` = 'playerID'

Link to comment
It isn't the best to do queries like ... WHERE `name` = '...' because that will get slow if you have a slightly bigger database. It would be the best to add a index for the name (ADD INDEX(`name`)). Even better would be to make your table id-driven - that's infact the best solution so you literally ADD PRIMARY KEY(`id`) and make it AUTO_INCREMENT. If not using phpMyAdming then ALTER TABLE sql-command will help you to make it AUTO_INCREMENT.

Then you would literally load the `id` into a variable for the player. Then you can easily run queries optimized and faster, like this

-- where playerID is the database ID for the player
executeSQLUpdate("yourTableName", "`SpawnX` = '" .. x .. "', `SpawnY` = '" .. y .. "', `SpawnZ` = '" .. z .. "'", "`id` = '" .. playerID .. "'")

That would make it

UPDATE `yourTableName` SET `SpawnX` = '.. x ..', `SpawnY` = ' .. y .. ', `SpawnZ` = ' .. z .. ' WHERE `id` = 'playerID'

So it would be faster if the Database would look like this?

ID, NAME, GELD, etc

ID would be auto increment

Link to comment

OK, it works now. Thanks! :D

But if I don't use "Autoincrement", can I get the number from the rowid with a function? And, if I understood it right: If I would make something with Autoincrement and it looks like this:

1,539

2,611

3,599

And I would delete number 2, Autoincrement would never re-use the number 2, but rowid would?

Link to comment

there is already autoincrementing index "rowid", you don't need to create it.

you can use it as you want, let it autoincrement itself or set your own value as with any other column, if you need to.

and you can access by "rowid" name in your every table.

and no, deleted IDs are not reused (until highest integer reached), autoincrementing goes from largest ID.

Link to comment
executeSQLQuery("UPDATE yourTableName SET SpawnX = '" .. x .. "', SpawnY = '" .. y .. "', SpawnZ = '" .. z .. "' WHERE Name = '" .. getPlayerName(thePlayer) .. "'")

Something like that seems problematic to me or is SQL Injection no issue in MTA/SQLite? Why not use parameter binding?

executeSQLQuery("UPDATE yourTableName SET SpawnX = ?, SpawnY = ?, SpawnZ = ? WHERE Name = ?",x,y,z,getPlayerName(theplayer))

Of course it depends on where you get your data from. But even something like the player name is a value the user inputs. I don't know if or how much damage could be done, but I guess parameter binding should be safer.

Link to comment

I thought it's immune against SQL Injections:

The advantage of using executeSQLQuery is that it is immune to users trying to exploit the query with an SQL injection attack. playerName may contain special characters like ', " or -- that will not influence the query, unlike the older approach where playerName would be concatenated into the query string.

This is what it says in the WIKI

Link to comment
I thought it's immune against SQL Injections:
The advantage of using executeSQLQuery is that it is immune to users trying to exploit the query with an SQL injection attack. playerName may contain special characters like ', " or -- that will not influence the query, unlike the older approach where playerName would be concatenated into the query string.

This is what it says in the WIKI

Yes, but only if you use parameter binding. If you just put the values directly in the query string, it can't escape them to prevent SQL Injection. The function doesn't prevent you from doing it 'wrong'. Just use the method with the questionmarks, that way the function will know which part of the query is a value and create a safe query for you.

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