kevin433 Posted February 21, 2010 Share Posted February 21, 2010 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
Aibo Posted February 21, 2010 Share Posted February 21, 2010 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 Link to comment
kevin433 Posted February 21, 2010 Author Share Posted February 21, 2010 Thanks! It works! I thought I had to use executeSQLInsert, or do I have to use it if the value in the database is 0 NULL? Link to comment
Aibo Posted February 21, 2010 Share Posted February 21, 2010 INSERT adds a new row to the table UPDATE updates already existing row you should probably read about basic SQL syntax 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
kevin433 Posted February 21, 2010 Author Share Posted February 21, 2010 Yeah, I should read about basic SQL syntax's I'm one of thoses who are learning more from trying than from reading Link to comment
Andre9977 Posted February 22, 2010 Share Posted February 22, 2010 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
kevin433 Posted February 22, 2010 Author Share Posted February 22, 2010 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
vovo4ka Posted February 22, 2010 Share Posted February 22, 2010 So it would be faster ID would be auto increment Sure It is much easier and faster to work with the database using the indices. But I have a trouble with AUTOINCREMENT on executeSQLInsert. I'm try executeSQLQuery and it works fine Link to comment
kevin433 Posted February 22, 2010 Author Share Posted February 22, 2010 How do I use Auto increment? My code is like this: function createVehicleDB() executeSQLCreateTable("Vehicles", "CarID INT AUTO_INCREMENT PRIMARY KEY, ModelID INT") end addEventHandler("onResourceStart", getRootElement(), createVehicleDB) Link to comment
Aibo Posted February 22, 2010 Share Posted February 22, 2010 in SQLite it's "INTEGER PRIMARY KEY" or "INTEGER PRIMARY KEY AUTOINCREMENT" actually, every table in SQLite already has autoincrementing index column called "rowid" (check in your SQL manager). and if you create another id column with "INTEGER PRIMARY KEY", it'll be just an alias of "rowid". http://www.sqlite.org/autoinc.html Link to comment
kevin433 Posted February 22, 2010 Author Share Posted February 22, 2010 OK, it works now. Thanks! 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
Aibo Posted February 22, 2010 Share Posted February 22, 2010 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
driver2 Posted February 23, 2010 Share Posted February 23, 2010 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
kevin433 Posted February 24, 2010 Author Share Posted February 24, 2010 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
driver2 Posted February 24, 2010 Share Posted February 24, 2010 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
kevin433 Posted February 24, 2010 Author Share Posted February 24, 2010 Ok, thanks Did that now. Link to comment
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now