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

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

Required Arguments

• query: An SQL query. Positions where parameter values will be inserted are marked with a ?.
• param1 [, var param2 ... ]: 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 quoted and escaped as required. (If you do not want a string quoted, use ??). Make sure that numbers are in number format as a string number is treated differently.

Returns

• table|false: result

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:

The table is of the format
{ { colname1=value1, colname2=value2, ... }, { colname1=value3, colname2=value4, ... }, ... }

Code Examples

Example 1 Example 2

server

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)

server

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

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.

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)

See Also

Database Functions

• dbConnect
• dbExec
• dbFree
• dbPoll
• dbPrepareString
• dbQuery
• executeSQLQuery

Reference

• Scripting Functions