Database with oxmysql · Lesson 2/3 · 8 min

Parameterized queries: close the door to SQL injection

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

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

The hole (NEVER do this)

lua

-- ❌ DANGER: concatenates the name directly
local nombre = pedidoDelJugador
MySQL.query.await("SELECT * FROM users WHERE name = '" .. nombre .. "'")
-- if nombre = "'; DROP TABLE users; --"  →  goodbye database

SQL injection

The correct way

lua

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

-- Several parameters, in order
MySQL.update.await(
  'UPDATE users SET money = ?, bank = ? WHERE identifier = ?',
  { cash, bank, identifier }
)

Parameterized

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

Practice what you learned

0/3

Corrige el error

Esta consulta concatena el nick del jugador (inyección SQL). Reescríbela parametrizada con ?.

Este código tiene un fallo:

1MySQL.query.await("SELECT * FROM characters WHERE nick = '" .. nick .. "'")

Reescríbelo corregido:

Pista

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

Test

¿Por qué es peligroso meter el texto del jugador en la consulta concatenándolo con ..?

Pista

Piensa en qué pasa si el nick contiene comillas y un ; DROP TABLE.

Rellena los huecos

Completa el UPDATE parametrizado con varios valores, en el orden correcto.

1MySQL.update.await(

2 'UPDATE users SET money = ?, bank = ? WHERE identifier = ?',

3 { cash, , }

4)

Pista

Los parámetros van en el MISMO orden que los ? de la consulta: money, bank, identifier.

Challenge: code it yourself

You're given a query that concatenates the player's nick 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

Pull the value out of the string and put it in the parameter table { nick }.

Escribe aquí tu solución:

← oxmysql: read and write to the database Next: Designing your resource's table

How was this lesson?