Editing SQL injection (section)

=== Deprecated/secondary approaches ===
String escaping is generally discouraged as a primary defense against SQL injection. [[OWASP]] describes this approach as "frail compared to other defenses" and notes that it may not be effective in all situations. Instead, OWASP recommends using "parameterized queries, stored procedures, or some kind of Object Relational Mapper (ORM) that builds your queries for you" as more reliable methods for mitigating SQL injection risks.<ref name=":0" />

==== 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.