To use parameterized queries, you need to use Mysqli or PDO. To rewrite your example using mysqli we would need the following.

prepare("INSERT INTO table (column) VALUES (?)"); // "s" means the database expects a string $stmt->bind_param("s", $variable); $stmt->execute();

The key function you need to read ismysqli::prepare.

Also, as others have suggested, you may find using something likePDO.

Please note that the case you are asking about is fairly simple, more complex cases may require more sophisticated methods. in particular:

• If you want to change the SQL structure based on user input, parameterized queries will not help, andmysql_real_escape_stringdoes not contain the required escapes. In this case, you'd be better off passing the user's input through a whitelist to ensure only "safe" values are allowed through.

No matter which database you use, thecorrectway to avoid SQL injection attacks is toseparate the data from the SQLso that the data is still data and> never Will be interpreted as a command by the SQL parser. It is possible to create SQL statements with properly formatted data portions, but if youdon't understand the details at all, you should alwaysuse prepared statements and parameterized queries.is a SQL statement sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

1. Using

   PDO(for any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name'); $stmt->execute([ 'name' => $name ]); foreach ($stmt as $row) { // Do something with $row }

2. Using

   MySQLi(for MySQL):
   Starting with PHP 8.2, we can useexecute_query() to prepare, bind parameters and execute SQL statements in one method:

   $result = $db->execute_query('SELECT * FROM employees WHERE name = ?', [$name]); while ($row = $result->fetch_assoc()) { // Do something with $row }

   Up to PHP8.1:

   $stmt = $db->prepare('SELECT * FROM employees WHERE name = ?'); $stmt->bind_param('s', $name); // 's' specifies the variable type => 'string' $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // Do something with $row }

If you are connecting to a database other than MySQL, you can refer to the second driver-specific option (for example,

pg_prepare()andpg_execute()for PostgreSQL) . PDO is a universal option.

---

Set up the connection correctly

PDO

Please note that when using

PDOto access a MySQL database,realprepared statementsare not used by default. To resolve this issue, you must disable simulation of prepared statements. An example of creating a connection usingPDOis:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4', 'user', 'password'); $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the example above, error mode is not strictly necessary,

but it is recommended to add it. This way, PDO will notify you of all MySQL errors by throwingPDOException.

However,

forcingis the firstsetAttribute()line, which tells PDO to disable simulated prepared statements and userealprepared statement statements . This ensures that statements and values are not parsed by PHP before being sent to the MySQL server (giving a would-be attacker no chance to inject malicious SQL).

While you can set the

character setin the options of the constructor, it is important to note that "older" versions of PHP (prior to 5.3.6)silently ignore the Character set parameters.

Mysqli

For mysqli we have to follow the same routine:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting $dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test'); $dbConnection->set_charset('utf8mb4'); // charset

---

illustrate

The SQL statements you pass to

prepareare parsed and compiled by the database server. You tell the database engine where you want to filter by specifying parameters (?or named parameters, like:namein the example above). Then, when you callexecute, the prepared statement is combined with the parameter values you specified.

What matters here is that the parameter value is combined with the compiled statement, not the SQL string. SQL injection works by tricking a script into containing a malicious string when it creates the SQL to be sent to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something unexpected.

Any parameters you send when using prepared statements will be treated as strings (although the database engine may do some optimizations, so parameters may of course end up being treated as numbers as well). In the example above, if the$namevariable contains'Sarah'; DELETE FROMEmployeesthe result is simply the search string"'Sarah'; DELETE FROMEmployees", and You will not end up withan empty table.

Another benefit of using prepared statements is that if you execute the same statement multiple times in the same session, it will only be parsed and compiled once, thus improving speed.

Oh, since you asked how to do the insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)'); $preparedStatement->execute([ 'column' => $unsafeValue ]);

---

Can prepared statements be used for dynamic queries?

While you can still use prepared statements with query parameters, the structure of the dynamic query itself cannot be parameterized, and certain query functions cannot be parameterized either.

For these specific scenarios, the best approach is to use a whitelist filter to limit the possible values.

// Value whitelist // $dir can only be 'DESC', otherwise it will be 'ASC' if (empty($dir) || $dir !== 'DESC') { $dir = 'ASC'; }