ExecuteSQLQuery: Difference between revisions

From Multi Theft Auto: Wiki
Jump to navigation Jump to search
mNo edit summary
(Undo revision 66548 by Abdo Nour (talk))
Tag: Undo
 
(24 intermediate revisions by 14 users not shown)
Line 3: Line 3:


This function executes an arbitrary SQL query and returns the result rows if there are any. It allows parameter binding for security (SQL injection is rendered impossible).
This function executes an arbitrary SQL query and returns the result rows if there are any. It allows parameter binding for security (SQL injection is rendered impossible).
{{Note|This function only acts upon registry.db. Use [[dbQuery]] to query a custom SQL database.}}


==Syntax==
==Syntax==
<syntaxhighlight lang="lua">
<syntaxhighlight lang="lua">
table executeSQLQuery(string query [, var param1 [, var param2 ...]])
table executeSQLQuery ( string query [, var param1 [, var param2 ... ] ] )
</syntaxhighlight>
</syntaxhighlight>


Line 14: Line 15:
===Optional Arguments===
===Optional Arguments===
*'''paramX:''' A variable number of parameters. These must be strings or numbers - it is important to make sure they are of the correct type. Also, the number of parameters passed must be equal to the number of "?" characters in the query string.
*'''paramX:''' A variable number of parameters. These must be strings or numbers - it is important to make sure they are of the correct type. Also, the number of parameters passed must be equal to the number of "?" characters in the query string.
String parameters are automatically escaped by adding a backslash (\) before ' and \ characters.


===Returns===
===Returns===
Returns a table with the result of the query if it was a SELECT query, or ''false'' if otherwise. In case of a SELECT query the result table may be empty when there are no result rows. The table is of the form:
Returns a table with the result of the query if it was a SELECT query, or ''false'' if otherwise. In case of a SELECT query the result table may be empty (if there are no result rows). The table is of the form:


<syntaxhighlight lang="lua">
<syntaxhighlight lang="lua">
Line 25: Line 27:
}
}
</syntaxhighlight>
</syntaxhighlight>
A subsequent table represents the next row.


==Example==
==Example==
Below are examples of equivalents for the deprecated executeSQL functions. Note that ` (backtick) can optionally be used to surround table and row names. It usually a good idea to do this to avoid name clashes with SQL reserved words.
Example equivalents for executeSQLCreateTable:
<syntaxhighlight lang="lua">
executeSQLQuery("CREATE TABLE IF NOT EXISTS players (clothes_head_texture TEXT, clothes_head_model TEXT, name TEXT)")
executeSQLQuery("CREATE TABLE IF NOT EXISTS `players` (`clothes_head_texture` TEXT, `clothes_head_model` TEXT, `name` TEXT)")
</syntaxhighlight>
Example equivalents for executeSQLDelete:
<syntaxhighlight lang="lua">
playerName = getPlayerName(thePlayer)
executeSQLQuery("DELETE FROM players WHERE name=?", playerName)
executeSQLQuery("DELETE FROM `players` WHERE `name`=?", playerName)
</syntaxhighlight>
Example equivalents for executeSQLDropTable:
<syntaxhighlight lang="lua">
executeSQLQuery("DROP TABLE players" )
executeSQLQuery("DROP TABLE `players`" )
</syntaxhighlight>
Example equivalents for executeSQLSelect:
<syntaxhighlight lang="lua">
playerName = getPlayerName(thePlayer)
executeSQLQuery("SELECT score,health FROM players WHERE name=?", playerName )
executeSQLQuery("SELECT `score`,`health` FROM `players` WHERE `name`=?", playerName )
</syntaxhighlight>
Example equivalents for executeSQLInsert:
<syntaxhighlight lang="lua">
playerName = getPlayerName(thePlayer)
colorName = "Blue"
soundName = "sound.mp3"
executeSQLQuery("INSERT INTO players(name,color,sound) VALUES(?,?,?)", playerName, colorName, soundName )
executeSQLQuery("INSERT INTO `players`(`name`,`color`,`sound`) VALUES(?,?,?)", playerName, colorName, soundName )
</syntaxhighlight>
Example equivalents for executeSQLUpdate:
<syntaxhighlight lang="lua">
playerName = getPlayerName(thePlayer)
colorName = "Blue"
soundName = "sound.mp3"
executeSQLQuery("UPDATE players SET color='green',sound='somehead' WHERE name=?", playerName )
executeSQLQuery("UPDATE players SET color=?,sound=? WHERE name=?", colorName, soundName, playerName )
executeSQLQuery("UPDATE `players` SET `color`=?,`sound`=? WHERE `name`=?", colorName, soundName, playerName )
</syntaxhighlight>
This example defines a console command that shows the ID's and names of all registered (stored in database) players that have more than the specified amount of money.
This example defines a console command that shows the ID's and names of all registered (stored in database) players that have more than the specified amount of money.


Line 48: Line 99:
     local result = executeSQLQuery("SELECT money FROM players WHERE name=?", playerName)
     local result = executeSQLQuery("SELECT money FROM players WHERE name=?", playerName)
     if(#result == 0) then
     if(#result == 0) then
         outputConsole("No player named " .. playerName " .. is registered.", thePlayer)
         outputConsole("No player named " .. playerName .. " is registered.", thePlayer)
     else
     else
         outputConsole("Money amount of player " .. playerName .. " is " .. result[1].money, thePlayer)
         outputConsole("Money amount of player " .. playerName .. " is " .. result[1].money, thePlayer)
Line 56: Line 107:
</syntaxhighlight>
</syntaxhighlight>


Notice the lack of single quotes around the "?" in this example, even though it represents a string. ExecuteSQLQuery will see that the playerName variable is a string and take care of the correct execution of the query by itself.
Notice the lack of single quotes around the "?" in this example, even though it represents a string. executeSQLQuery will see that the playerName variable is a string and take care of the correct execution of the query by itself.


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.
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.
==See Also==
{{Registry_functions}}
[[ru:executeSQLQuery]]

Latest revision as of 21:33, 15 May 2020

This function executes an arbitrary SQL query and returns the result rows if there are any. It allows parameter binding for security (SQL injection is rendered impossible).

[[{{{image}}}|link=|]] Note: This function only acts upon registry.db. Use dbQuery to query a custom SQL database.

Syntax

table executeSQLQuery ( string query [, var param1 [, var param2 ... ] ] )

Required Arguments

  • query: An SQL query. Positions where parameter values will be inserted are marked with a "?".

Optional Arguments

  • paramX: A variable number of parameters. These must be strings or numbers - it is important to make sure they are of the correct type. Also, the number of parameters passed must be equal to the number of "?" characters in the query string.

String parameters are automatically escaped by adding a backslash (\) before ' and \ characters.

Returns

Returns a table with the result of the query if it was a SELECT query, or false if otherwise. In case of a SELECT query the result table may be empty (if there are no result rows). The table is of the form:

{
    { colname1=value1, colname2=value2, ... },
    { colname1=value3, colname2=value4, ... },
    ...
}

A subsequent table represents the next row.

Example

Below are examples of equivalents for the deprecated executeSQL functions. Note that ` (backtick) can optionally be used to surround table and row names. It usually a good idea to do this to avoid name clashes with SQL reserved words.

Example equivalents for executeSQLCreateTable:

executeSQLQuery("CREATE TABLE IF NOT EXISTS players (clothes_head_texture TEXT, clothes_head_model TEXT, name TEXT)")
executeSQLQuery("CREATE TABLE IF NOT EXISTS `players` (`clothes_head_texture` TEXT, `clothes_head_model` TEXT, `name` TEXT)")

Example equivalents for executeSQLDelete:

playerName = getPlayerName(thePlayer)
executeSQLQuery("DELETE FROM players WHERE name=?", playerName)
executeSQLQuery("DELETE FROM `players` WHERE `name`=?", playerName)

Example equivalents for executeSQLDropTable:

executeSQLQuery("DROP TABLE players" )
executeSQLQuery("DROP TABLE `players`" )

Example equivalents for executeSQLSelect:

playerName = getPlayerName(thePlayer)
executeSQLQuery("SELECT score,health FROM players WHERE name=?", playerName )
executeSQLQuery("SELECT `score`,`health` FROM `players` WHERE `name`=?", playerName )

Example equivalents for executeSQLInsert:

playerName = getPlayerName(thePlayer)
colorName = "Blue"
soundName = "sound.mp3"
executeSQLQuery("INSERT INTO players(name,color,sound) VALUES(?,?,?)", playerName, colorName, soundName )
executeSQLQuery("INSERT INTO `players`(`name`,`color`,`sound`) VALUES(?,?,?)", playerName, colorName, soundName )

Example equivalents for executeSQLUpdate:

playerName = getPlayerName(thePlayer)
colorName = "Blue"
soundName = "sound.mp3"
executeSQLQuery("UPDATE players SET color='green',sound='somehead' WHERE name=?", playerName )
executeSQLQuery("UPDATE players SET color=?,sound=? WHERE name=?", colorName, soundName, playerName )
executeSQLQuery("UPDATE `players` SET `color`=?,`sound`=? WHERE `name`=?", colorName, soundName, playerName )


This example defines a console command that shows the ID's and names of all registered (stored in database) players that have more than the specified amount of money.

function listPlayersWithMoreMoneyThan(thePlayer, command, amount)
    local players = executeSQLQuery("SELECT id, name FROM players WHERE money > ?", tonumber(amount))
    outputConsole("Players with more money than " .. amount .. ":", thePlayer)
    for i, playerdata in ipairs(players) do
        outputConsole(playerdata.id .. ": " .. playerdata.name, thePlayer)
    end
end

addCommandHandler("richplayers", listPlayersWithMoreMoneyThan)


This example shows the amount of money a certain registered player has.

function showPlayerMoney(thePlayer, command, playerName)
    local result = executeSQLQuery("SELECT money FROM players WHERE name=?", playerName)
    if(#result == 0) then
        outputConsole("No player named " .. playerName .. " is registered.", thePlayer)
    else
        outputConsole("Money amount of player " .. playerName .. " is " .. result[1].money, thePlayer)
    end
end
addCommandHandler("playermoney", showPlayerMoney)

Notice the lack of single quotes around the "?" in this example, even though it represents a string. executeSQLQuery will see that the playerName variable is a string and take care of the correct execution of the query by itself.

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.

See Also