Database with oxmysql · Lesson 2/3 · 8 min

Parameterized queries: close off SQL injection

Concatenating text into a query is the most classic hole. Placeholders (?) close it off.

SQL injection happens when you put user data inside the query as text. A malicious name could wipe out your database. The solution is placeholders ?.

The hole (NEVER do this)

lua

-- ❌ DANGER: concatenates the data directly
local cid = datoDelJugador
MySQL.query.await("SELECT * FROM players WHERE citizenid = '" .. cid .. "'")
-- if cid = "'; DROP TABLE players; --"  →  goodbye database

SQL injection

The right way

lua

-- ✅ The ? is filled in safely, escaping the value
MySQL.query.await('SELECT * FROM players WHERE citizenid = ?', { cid })

-- Several parameters, in order
MySQL.update.await(
  'UPDATE player_houses SET price = ?, label = ? WHERE id = ?',
  { precio, etiqueta, id }
)

Parameterized

Rule with no exceptions: EVERY value you don't write by hand goes as a ? in the parameter table. Never with .. inside the SQL. It also applies to numbers (cleaner and more consistent).

Practice what you learned

0/3

Test

¿Por qué es peligroso construir la query con "... WHERE name = '" .. nombre .. "'"?

Corrige el error

Esta consulta concatena el citizenid del jugador dentro del string. Reescríbela parametrizada con ? para cerrar la inyección SQL.

Este código tiene un fallo:

1local citizenid = Player.PlayerData.citizenid

2local filas = MySQL.query.await("SELECT * FROM player_vehicles WHERE citizenid = '" .. citizenid .. "'")

Reescríbelo corregido:

Pista

Saca el citizenid del string y mételo en la tabla de parámetros: ('... WHERE citizenid = ?', { citizenid }).

Rellena los huecos

Completa el UPDATE parametrizado que cambia el dinero de un jugador buscándolo por su citizenid.

1local citizenid = Player.PlayerData.citizenid

2MySQL.update.await('UPDATE players SET money = WHERE citizenid = ', { dinero, citizenid })

Pista

Cada valor que no escribas a mano va como ?, y en el array { } van en el MISMO orden que aparecen los ?.

Challenge: code it yourself

You're given a query that concatenates the player's citizenid to look them up. Rewrite it parameterized.

Write it yourself in your editor (VS Code) and test it on your server. You learn here by doing it, not by copying.

See hint

Take the value out of the string and put it into the parameter table { cid }.

Escribe aquí tu solución:

← oxmysql: reading and writing to the database Next: Designing your resource's table (with citizenid)

How was this lesson?