Editing SQL injection (section)

== SQL injection mitigation ==
SQL injection is a well-known attack that can be mitigated with established security measures. However, a 2015 [[cyberattack]] on British telecommunications company [[TalkTalk Group|TalkTalk]] exploited an SQL injection vulnerability, compromising the personal data of approximately 400,000 customers. The ''[[BBC]]'' reported that security experts expressed surprise that a major company remained vulnerable to such an exploit.<ref>{{Cite news |date=October 26, 2015 |title=Questions for TalkTalk - BBC News |url=https://www.bbc.com/news/technology-34636308 |url-status=live |archive-url=https://web.archive.org/web/20151026113434/http://www.bbc.com/news/technology-34636308 |archive-date=October 26, 2015 |access-date=2015-10-26 |work=BBC News |language=en}}</ref>

A variety of defensive measures exist to mitigate SQL injection risks by preventing attackers from injecting malicious SQL code into database queries. Core mitigation strategies, as outlined by [[OWASP]], include parameterized queries, [[input validation]], and least privilege access controls, which limit the ability of user input to alter SQL queries and execute unintended commands.<ref name=":0" /> In addition to preventive measures, detection techniques help identify potential SQL injection attempts. Methods such as [[pattern matching]], [[software testing]], and grammar analysis examine query structures and user inputs to detect irregularities that may indicate an injection attempt.<ref name="sfw2.12018" />

=== Core mitigation ===

==== Parameterized statements ====
{{Main|Prepared statement}}
Most development platforms support parameterized statements, also known as placeholders or [[bind variable]]s, to securely handle user input instead of embedding it in SQL queries. These placeholders store only values of a defined type, preventing input from altering the query structure. As a result, SQL injection attempts are processed as unexpected input rather than executable code.  With parametrized queries, SQL code remains separate from user input, and each parameter is passed as a distinct value, preventing it from being interpreted as part of the SQL statement.<ref name=":0" />

==== Allow-list input validation ====
[[Allow-list]] input validation ensures that only explicitly defined inputs are accepted, reducing the risk of injection attacks. Unlike [[Deny list|deny-lists]], which attempt to block known malicious patterns but can be bypassed, allow-lists specify valid input and reject everything else. This approach is particularly effective for [[structured data]], such as dates and email addresses, where strict validation rules can be applied. While input validation alone does not prevent SQL injection and other attacks, it can act as an additional safeguard by identifying and filtering unauthorized input before it reaches an SQL query.<ref name=":0" /><ref>{{Cite web |title=Input Validation Cheat Sheet |url=https://cheatsheetseries.owasp.org/cheatsheets/Input_Validation_Cheat_Sheet.html |access-date=10 March 2025 |website=Open Web Application Security Project (OWASP)}}</ref>

==== Least privilege ====
According to OWASP, the [[principle of least privilege]] helps mitigate SQL injection risks by ensuring database accounts have only the minimum permissions necessary. Read-only accounts should not have modification privileges, and application accounts should never have administrative access.  Restricting database permissions is a key part of this approach, as limiting access to system tables and restricting user roles can reduce the risk of SQL injection attacks. Separating database users for different functions, such as authentication and data modification, further limits potential damage from SQL injection attacks.<ref name=":0" />

Restricting database permissions on the web application's database login further reduces the impact of SQL injection vulnerabilities. Ensuring that accounts have only the necessary access, such as restricting SELECT permissions on critical system tables, can mitigate potential exploits.{{Needs citation|date=March 2025}} 

On [[Microsoft SQL Server]], limiting SELECT access to system tables can prevent SQL injection attacks that attempt to modify database schema or inject malicious scripts. For example, the following permissions restrict a database user from accessing system objects:{{Citation needed|date=March 2025|reason=Citation needed to comply with WP:NOR (No Original Research) and WP:V (Verifiability)}}
<syntaxhighlight lang="tsql">
deny select on sys.sysobjects to webdatabaselogon;
deny select on sys.objects to webdatabaselogon;
deny select on sys.tables to webdatabaselogon;
deny select on sys.views to webdatabaselogon;
deny select on sys.packages to webdatabaselogon;
</syntaxhighlight>

=== Supplementary mitigation ===

==== Object relational mappers ====
[[Object–relational mapping]] (ORM) frameworks provide an [[object-oriented interface]] for interacting with [[Relational database|relational databases]]. While ORMs typically offer built-in protections against SQL injection, they can still be vulnerable if not properly implemented. Some ORM-generated queries may allow unsanitized input, leading to injection risks. Additionally, many ORMs allow developers to execute raw SQL queries, which if improperly handled can introduce SQL injection vulnerabilities.<ref>{{Cite web |title=Testing for ORM Injection |url=https://owasp.org/www-project-web-security-testing-guide/latest/4-Web_Application_Security_Testing/07-Input_Validation_Testing/05.7-Testing_for_ORM_Injection |access-date=17 March 2025 |website=OWASP}}</ref><ref>{{Cite web |date=February 13, 2021 |title=SQL Injection Attacks & Prevention: Complete Guide |url=https://www.appsecmonkey.com/blog/sql-injection-attack-and-prevention/ |access-date=2021-02-24 |website=appsecmonkey.com |language=en}}</ref>

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