Safer Database Code in FiveM Scripts with oxmysql: Placeholders, Atomic Updates and Transactions

Most bugs that wipe a FiveM server's economy are not clever exploits. They are ordinary database code: a query built with string concatenation, a "check balance, then subtract" done in two steps, or a loop that fires one query per row and stalls the server thread on restart. This post walks through four habits that fix most of these problems, using oxmysql, the MySQL resource most current frameworks depend on.

All examples are server-side Lua. To use the MySQL helper object, add the library to your resource's manifest:

-- fxmanifest.lua
server_scripts {
    '@oxmysql/lib/MySQL.lua',
    'server/*.lua',
}

1. Never build queries with string concatenation

This is the classic mistake:

-- Don't do this
local plate = data.plate
MySQL.query.await("SELECT * FROM owned_vehicles WHERE plate = '" .. plate .. "'")

data came from a client event, so the client controls plate. A value such as ' OR '1'='1 changes the meaning of the query. Use placeholders and pass values separately:

local row = MySQL.single.await(
    'SELECT owner, vehicle FROM owned_vehicles WHERE plate = ?',
    { plate }
)

oxmysql escapes the value for you. Named placeholders work too (@plate or :plate with a key/value table), which reads better when a query has many parameters. Two notes:

• Placeholders protect values, not identifiers. If you need a dynamic column or table name, pick it from a hard-coded whitelist in your script, never from client input.
• Pick the right helper. MySQL.single returns one row, MySQL.scalar returns one value, MySQL.insert returns the new id and MySQL.update returns the number of affected rows. Using the specific helper makes the calling code simpler and the intent obvious.

2. Make "check, then change" a single statement

A shop or bank script often does this:

-- Race condition
local balance = MySQL.scalar.await('SELECT balance FROM accounts WHERE id = ?', { accountId })
if balance >= amount then
    MySQL.update.await('UPDATE accounts SET balance = balance - ? WHERE id = ?', { amount, accountId })
    -- give item / transfer money
end

Between the SELECT and the UPDATE, another request for the same account can run. Two fast purchases both see the old balance, both pass the check, and the account goes negative or the player gets two items for the price of one.

Push the condition into the UPDATE and look at how many rows it touched:

local affected = MySQL.update.await(
    'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?',
    { amount, accountId, amount }
)

if affected == 1 then
    -- payment succeeded, now hand over the item
else
    -- not enough money (or the account does not exist)
end

The database applies the check and the change together, so there is no window for a second request to slip through. The same pattern works for stock counts (AND stock > 0), cooldown timestamps and one-time rewards.

If your framework keeps money in memory and saves it later, use the framework's own money functions instead. The rule still applies: keep the check and the change in one place that cannot be interrupted.

3. Wrap multi-step changes in a transaction

Some actions touch several tables. Selling a vehicle to another player might change the owner, clear stored garage data and write an audit log. If the server crashes or one query fails halfway through, you end up with a car that has no owner, or a log entry for a sale that never happened.

A transaction runs a set of queries and commits them only if all of them succeed:

local ok = MySQL.transaction.await({
    { query = 'UPDATE owned_vehicles SET owner = ? WHERE plate = ? AND owner = ?',
      values = { buyerId, plate, sellerId } },
    { query = 'DELETE FROM vehicle_storage WHERE plate = ?',
      values = { plate } },
    { query = 'INSERT INTO vehicle_sales (plate, seller, buyer, sold_at) VALUES (?, ?, ?, NOW())',
      values = { plate, sellerId, buyerId } },
})

if not ok then
    print(('[vehicles] transfer of %s failed and was rolled back'):format(plate))
end

The result is a single boolean. Note that a query affecting zero rows is not a failure: if the seller no longer owns the car, the first UPDATE changes nothing, but the transaction still commits the other two. When a step must change a row, run it on its own first with MySQL.update.await, check the count, and only then run the rest.

4. Batch instead of querying inside loops

On restart, a housing or garage script might load data like this:

-- One round trip per player
for _, playerId in ipairs(GetPlayers()) do
    local identifier = GetPlayerIdentifierByType(playerId, 'license')
    local rows = MySQL.query.await('SELECT * FROM player_houses WHERE owner = ?', { identifier })
    -- ...
end

With 100 players that is 100 sequential round trips. Fetch everything in one query and group it in Lua:

local rows = MySQL.query.await('SELECT owner, house_id, data FROM player_houses')
local byOwner = {}

for _, row in ipairs(rows) do
    byOwner[row.owner] = byOwner[row.owner] or {}
    byOwner[row.owner][#byOwner[row.owner] + 1] = row
end

For writes, MySQL.prepare accepts a list of parameter sets for one statement, so you can save many rows in a single call:

local params = {}
for plate, fuel in pairs(dirtyFuel) do
    params[#params + 1] = { fuel, plate }
end

if #params > 0 then
    MySQL.prepare.await('UPDATE owned_vehicles SET fuel = ? WHERE plate = ?', params)
end

prepare accepts only ? placeholders, not named ones, so keep that in mind when converting old queries.

Quick review checklist

When you install or audit a resource, search its server files for MySQL. and check:

• No query strings joined with .. around client data.
• Balance, stock and ownership checks done in the WHERE clause, with the affected-row count checked.
• Multi-table changes wrapped in a transaction.
• No queries inside loops over players, vehicles or items.
• Indexes on the columns used in WHERE (owner, plate, identifier). Without them, even correct queries slow down as tables grow.

Being able to grep for these patterns is one of the practical upsides of open source resources, such as the FiveM server scripts listed on xfivem.shop: you can read every query before it touches your production database, and fix it if needed.