Editing SQL injection (section)

==== String escaping ====
One of the traditional ways to prevent injections is to add ''every piece of data as a quoted string'' and [[Escape sequence|escape]] all characters, that have special meaning in SQL strings, in that data.<ref>{{cite web|title=MySQL String Literals|url=https://dev.mysql.com/doc/refman/8.4/en/string-literals.html|language=en}}</ref> The manual for an SQL DBMS explains which characters have a special meaning, which allows creating a comprehensive [[Blacklist (computing)|blacklist]] of characters that need translation.{{Citation needed|date=March 2025}} For instance, every occurrence of a single quote (<code>'</code>) in a string parameter must be prepended with a backslash (<code>\</code>) so that the database understands the single quote is part of a given string, rather than its terminator. [[PHP]]'s [[MySQLi]] module provides the <code>mysqli_real_escape_string()</code> function to escape strings according to [[MySQL]] semantics; in the following example the username is a string parameter, and therefore it can be protected by means of string escaping:{{Needs clarification|date=March 2025}}
<syntaxhighlight lang="php">
$mysqli = new mysqli('hostname', 'db_username', 'db_password', 'db_name');
$query = sprintf("SELECT * FROM `Users` WHERE UserName='%s'",
                  $mysqli->real_escape_string($username),
$mysqli->query($query);
</syntaxhighlight>

Besides, not every piece of data can be added to SQL as a string literal (MySQL's LIMIT clause arguments<ref>{{cite web|title=MySQL SELECT Statement|url=https://dev.mysql.com/doc/refman/8.4/en/select.html|language=en}}</ref> or table/column names<ref>{{cite web|title=MySQL Schema Object Names|url=https://dev.mysql.com/doc/refman/8.4/en/identifiers.html|language=en}}</ref> for example) and in this case escaping string-related special characters will do no good whatsoever, leaving resulting SQL open to injections.