Prepared Statements

Prepared statements

Prepared statements are the safe, fast way to run a query with values in it — especially values that come from players.

The idea

Instead of building one big SQL string with the values glued in, you:

1. prepare a query with ? placeholders,
2. bind a value to each ?,
3. execute.

The values travel separately from the SQL text, so they can never be interpreted as SQL. Injection becomes impossible by construction — there's no string to inject into.

new PreparedStatement:st = mysql_prepare(g_DB,
    "SELECT id, money FROM accounts WHERE name = ? AND banned = ?");

mysql_stmt_set_string(st, 1, playerName);   // first ?  (1-based, like JDBC)
mysql_stmt_set_int(st, 2, 0);               // second ?

mysql_stmt_execute(st, "OnLookup", "d", playerid);

forward OnLookup(playerid);
public OnLookup(playerid)
{
    new rows; mysql_rs_row_count(rows);
    if (!rows)  // no rows
    {
        SendClientMessage(playerid, -1, "No such account.");
        return 1;
    }
    new id, money;
    mysql_rs_get_int_by(0, "id", id);
    mysql_rs_get_int_by(0, "money", money);
    // ... use them ...
    return 1;
}

Binding functions

Function	Binds
mysql_stmt_set_int(st, idx, value)	an integer
mysql_stmt_set_float(st, idx, value)	a float
mysql_stmt_set_string(st, idx, value)	a string
mysql_stmt_set_null(st, idx)	SQL NULL

Indices are 1-based (? #1 = index 1), matching the JDBC/DB-API convention.

INSERT example (the safe way to save a player)

new PreparedStatement:st = mysql_prepare(g_DB,
    "INSERT INTO accounts (name, hash, ip) VALUES (?, ?, ?)");
mysql_stmt_set_string(st, 1, name);
mysql_stmt_set_string(st, 2, argon2hash);
mysql_stmt_set_string(st, 3, ip);
mysql_stmt_execute(st, "OnSaved", "d", playerid);

Closing

Free the statement when you're done with it (e.g. in the callback):

mysql_stmt_close(st);

omp-MySQL closes statements safely on the connection's worker thread, so it's fine to close from your callback. They're also all cleaned up on disconnect/restart.

When to use what

• Prepared statement — anything with player/user input, or a query you run a lot.
• mysql_format + %e — quick one-off queries where a prepared statement is overkill, but you still have a value to escape.
• Plain mysql_execute — fully static queries with no outside values.

Next: The mysql-admin demo →