Most modern websites and applications connect to databases programmed using Structured Query Language (SQL). SQL injection (SQLi) vulnerabilities arise when websites do not adequately screen, filter, or control the queries from the website, which permits attackers to attempt to inject fragments of SQL code into database queries to extract information.
To prevent SQLi attacks, web application and database programmers need to filter inputs, restrict database code, restrict database access, maintain, and monitor the application and database. While these five methods work, they apply mostly to code in development because existing code is often too lengthy to check line by line. Fortunately, various open source and commercial tools can guide dev teams to locate SQLi vulnerabilities and specialized vendors also provide outsourced assistance.
Featured PartnersFeatured Threat Intelligence Software
eSecurity Planet may receive a commission from merchants for referrals from this website
- How to Know if a Website or App is Vulnerable to a SQL Injection Attack
- SQLi FAQ: Frequently Asked Questions
- Bottom Line: Prevent SQL Injections
5 Key Methods to Prevent SQL Injection Attacks
For nearly 20 years, SQL injection vulnerabilities hovered near the top of the OWASP Top 10 Threat List, and SQL injection remains a dangerous and relevant threat. The good news is that website owners can proactively mitigate the danger.
The five key methods to prevent SQL injection attacks include:
- Filter database inputs: Detect and filter out malicious code from user inputs.
- Restrict database code: Prevent unintended database queries and exploration by limiting database procedures and code.
- Restrict database access: Prevent unauthorized data access, exfiltration, or deletion through access control restrictions.
- Maintain applications and databases: Keep databases fully patched and updated. Upgrade when possible.
- Monitor application and database inputs and communications: Monitor communication to detect and block malicious SQLi attempts.
Each method can be accomplished through various techniques that we explore in more detail below.
1. Filter Database Inputs
Although input filtering alone cannot stop SQL injection attacks, filtering database input from websites and applications provides fundamental security to eliminate SQL injection vulnerabilities. Many attackers attempt to exploit extended URLs and special character handling to explore databases and execute commands to gain unauthorized access or exfiltrate and delete data.
Deny Extended URLs
Attackers seek to learn about databases as part of the SQLi exploitation process. One tactic uses extended URLs to probe potential databases.
The International Journal of Research in Computer Applications and Robotics cites the example of sending the following browser query to the web server at www.store.com:
http://www.store.com/Search.php?product="<SCRIPT>alert(‘Hi…’) </SCRIPT>"
For databases that do not parse user input to check for HTML and javascript tags, this extended URL will reply to the attacker with:
Product “<SCRIPT>alert(„Hi…‟) </SCRIPT>” not found…
This clues in the attacker that the database can be further probed with scripts to explore the structure and possibly even used in Cross-site Scripting (XSS) attacks. Parsing input or denying extended URLs can eliminate this style of probing from attackers. However, keep in mind that some use of extended URLs can be legitimate and may not be able to be banned outright in all circumstances.
Sanitize Data and Limit Special Characters
Proper data sanitization and standardization provides a key component to safeguarding against SQL injection vulnerabilities. SQLi attackers abuse special characters to use a web interface to deliver SQL code to the database, so data must be sanitized to prevent concatenation or recognizing user input as commands.
For example, consider a login attempt where an attacker attempts to login using the password: password’ or 1=1
An unhardened SQL database would likely run a database query that verifies the password with some of the code reading:
password = ‘<insert user input here>’
One the database processes the attacker’s string, the database will see the command:
password = ‘password’ or 1=1’
This maliciously introduces a ‘true’ statement (1=1) into the database query and the database would interpret the command as: allow access if the password is correct or if 1 = 1. Thus access will be granted even with invalid passwords.
Different programming languages will use different specific commands to filter the text, so programmers need to check the latest options, but often built-in SQL Sanitization Libraries can provide the best options for effective code.
To illustrate one possibility, in MySQL developers use mysqli_real_escape_string() to capture the text input instead of passing the text form input directly to the database. PHP.net provides a thorough example of how to implement escaping, but as an example in object-oriented style PHP:
$query = sprintf("SELECT CountryCode FROM City WHERE name='%s'",
$mysqli->real_escape_string($city));
$result = $mysqli->query($query);
Using this command ensures that even a command entered by an attacker would be converted to a string of text, which can ensure that any dangerous characters such as a single quote ‘ are not passed to a SQL query.
Another method to sanitize the data input involves typecasting. With typecasting the data input will be restricted to the data format expected from the field. For example the following command would restrict the ‘id’ variable to an integer:
$id = (int)$_POST[“id”]
While typecasting can be very useful, it is more limited in application and will not be as commonly used.
2. Restrict Database Code
Input filtering is a good starting point, but attackers can find other ways to bypass inputs using zero-day vulnerabilities, credentials compromise, and more. Organizations can restrict the code available to a database to further control and limit the ability of attackers to exploit SQL injection vulnerabilities.
Database managers should reduce functionality, use stored procedures, whitelist user inputs, and enforce prepared statements and parameterization. These tactics limit the database strictly to the capabilities needed for the task and prevent unexpected uses and exploits.
See the Top 20 Database Security Solutions
Reduce Available Functionality
In cybersecurity, an attack surface refers to the array of potential entry points for attackers. In the context of SQLi attacks, reducing the attack surface requires the disabling of any unneeded database functionalities.
One such example is the xp_cmdshell extended stored procedure in the Microsoft SQL Server. This procedure can spawn a Windows command shell and pass a string for execution. Because the Windows process generated by xp_cmdshell has the same security privileges as the SQL Server service account, unrestricted availability of this procedure allows attackers to cause severe damage.
Use Stored Procedures In the Database
Using stored procedures can isolate the database from the users and prevent some of the exploitations. Instead of executing code directly on the database, the app will activate stored procedures and return the results.
Using stored procedures also requires variable binding. Stored procedures reside in the database and are called from the web application. Stored procedures are not immune to SQLi vulnerabilities if dynamic SQL generation is used.
Whitelist User Inputs
Exploiting SQLi often requires the database to respond to abnormal processes and procedures as the attacker explores the database. A common first step to preventing SQL injection attacks is validating user inputs using whitelisting or allow lists.
A developer will identify the essential SQL statements and establish a whitelist for all valid SQL statements, leaving unvalidated statements out of the query. This process is known as input validation or query redesign.
Additionally, inputs should be configured for user data by context. For example, input fields for email addresses can be filtered to allow only the characters in an email address, such as a required “@” character. Similarly, phone numbers and social security numbers should only be filtered to allow the specific number of digits for each.
While this action alone won’t stop SQLi attackers, it is an added barrier to a common fact-finding tactic for SQL injection attacks.
Also read: How to Use Input Sanitization to Prevent Web Attacks
Enforce Prepared Statements And Parameterization
Organizations should use prepared statements with parameterized queries, also known as variable binding, for writing all database queries. By defining all SQL code involved with queries, or parameterization, the database can easily distinguish between user input and code without the SQLi risk.
Prepared statements provide a fundamental and critical defense against SQL injection vulnerabilities. Where possible, developers should attempt to implement prepared statements so that a database will treat malicious SQL statements as data and not as a potential command.
However, they may not be suitable for all needs, especially those that require dynamic SQL. In these situations, SQLi vulnerabilities must be accepted as a possibility for code instructions and other tactics (such as whitelisting, user input sanitization, etc.) must be used.
3. Restrict Database Access
At some point, a user’s credentials will become compromised or an unknown vulnerability in a web application or database or server will be exploited by a skilled attacker. To minimize potential damage from the subsequent SQLi attack:
- external access should be limited with firewalls
- user access should be limited to minimal error messages, database functions, and database tables
- potential gains from a breach should be limited through encryption and minimal use of shared accounts
Raise Virtual Or Physical Firewalls
A software or appliance-based web application firewall (WAF) helps filter out malicious data and attacks. Modern firewalls, including NGFW and FWaaS offerings, deploy a comprehensive set of default rules and the ease to change configurations as needed. If a patch or update has yet to be released, WAFs can provide initial protection or mitigation against exposed vulnerabilities.
A popular example is the free, open-source module ModSecurity, available for Apache, Microsoft IIS, and nginx web servers. ModSecurity provides a sophisticated and ever-evolving set of rules to filter potentially dangerous web requests. Its SQL injection defenses can catch most attempts to sneak SQL through web channels.
Don’t Divulge More Than Necessary In Error Messages
SQL injection attackers can learn a great deal about database architecture from error messages. To block exploration of this type, ensure that error messages display minimal information.
Use the “RemoteOnly” customErrors mode (or equivalent) in a database to limit display of verbose error messages to the local machine and only deliver “unhandled error” messages to external users and potential attackers. This tactic adds additional safeguards to obscure the organization’s internal database structure, table names, or account names.
Establish Appropriate Privileges And Strict Access
Given the power many SQL databases hold for an organization, it’s imperative to enforce least privilege access policies with strict rules. If a website only requires the use of SELECT statements for a database, there’s no reason it should have additional INSERT, UPDATE, or DELETE privileges.
Further, a database should only be accessed with admin-level privileges when necessary. Using a limited access account is far safer for general activity and ultimately limits an attacker’s access in the event the less-privileged admin credential is compromised. PAM tools can even be used to provide temporary admin credentials upon demand to further control admin privileges.
Limit Read-Access
The read-access configuration of the database implements a form of least-privilege to protect against SQLi. A compromised credential or unknown SQLi vulnerability will have more limited ability to extract information when the associated access is managed and limited to a subset of database tables.
Encryption: Keep Your Secrets Secret
Encryption is almost universally employed as a data protection technique and for a good reason. Without appropriate encryption and hashing policies, sensitive information could be in plain sight for an intruder. While only a part of the security checklist, Microsoft notes encryption, “transforms the problem of protecting data into a problem of protecting cryptographic keys.”
It’s best to assume internet-connected applications will become compromised at some point. Therefore encryption should be applied to passwords, confidential data, and connection strings so that any data extracted from a successful SQLi attack should also be encrypted and therefore of less use to the attacker.
Limit or Eliminate Shared Databases Or User Accounts
Shared databases by multiple websites or applications can be a recipe for disaster. And the same is true for user accounts that have access to multiple web applications. This shared access might provide flexibility for the managing organization or administrator, but it also unnecessarily poses a more significant security risk in the event of application or user credentials compromise.
Ideally, any linked servers, storage area networks (SANs), or cloud data buckets should have minimal access to the target server and access is limited strictly to mission-critical data. All linked assets should have distinct logins from any process on the target server.
4. Maintain Applications And Databases
Vulnerabilities in applications and databases that are exploitable using SQL injection are regularly discovered and publicly identified. Organizations must stay current with vulnerability news and vendor announcements to obtain and apply patches or updates as soon as practical.
For SQLi purposes, all components of a web application must be monitored and updated, including database server software, frameworks, libraries, plug-ins, application programming interfaces (APIs) and web server software. For organizations that struggle to consistently patch and update programs, a patch management solution might be worth the investment to relieve some of the burden from the IT and application development teams.
5. Monitor Application And Database Inputs And Communications
Organizations or third-party vendors should continually monitor all SQL statements of database-connected applications. Monitoring should focus on documenting activity for database accounts, prepared statements, and stored procedures.
Monitoring enables more effective identification of rogue SQL statements and vulnerabilities. Once identified, admins can delete and disable unnecessary accounts, prepared statements, and stored procedures.
Monitoring can be further enhanced through the utilization of machine learning and behavioral analysis embedded in advanced Privileged Access Management (PAM) and Security Incident and Event Management (SIEM) tools.
How to Know if a Website or App is Vulnerable to a SQL Injection Attack
Any website that interacts with an SQL database is potentially at risk for SQLi attacks. While programmers and database administrators should always keep the five key methods to prevent SQLi attacks in mind, developers can make mistakes and not every programming team can enact best practices at all times.
To detect potential issues in existing applications and databases, security teams can deploy automatic detection for SQL injection vulnerabilities, utilize detection tools, or engage specialist vendors.
See the Top Code Debugging and Code Security Tools
Testing For SQL Injection Vulnerabilities
There are several free or commercial penetration tools an organization can use to identify potential SQL injection vulnerabilities.
Typically, these penetration testing tools start by probing a website to determine what type of database is in use. With that knowledge, the program can build queries to examine the characteristics of the database. With little SQL expertise required from the end-user, the detection tool can potentially extract fields, tables, and sometimes even full data dumps from a target.
Perhaps most importantly, many tools offer an error-fixing feature that can help remove some of the vulnerabilities discovered. Many powerful SQL injection tools are available open-source, therefore organizations must test applications before attackers use those tools to find and exploit potential vulnerabilities.
Utilizing an SQLi Detection Tool
Several cybersecurity vendors and open source developers also offer specialized, automatic SQL injection tools to identify potential vulnerabilities. For open-source detection tools, SQLMap and jSQL continue to be two of the most popular, with others including:
- BBQSQL
- Blind-SQL-Bitshifting
- Blisqy
- Damn Small SQLi Scanner (DSSS)
- explo
- Leviathan
- NoSQLMap
- Tyrant-SQL
- Whitewidow
SQLi FAQ: Frequently Asked Questions
What is the Best Protection Against SQL Injection?
For better or worse, there is no single technique that will provide the ‘best’ protection against SQL Injection attacks. It is easy to say “Input Validation” provides the best protection, but to execute input validation well requires the combination of several techniques (sanitized data, stored procedures, whitelist user inputs, etc.).
Which Queries Help Prevent SQL Injection?
When designing database operations, the use of prepared statements with parameterized queries and stored procedures will help to prevent SQLi attacks.
Does Encryption Protect Against SQL Injection?
Encryption can protect against SQLi in two indirect ways. Encrypting the data within the database does not block SQL injection attacks; however, it will reduce the damage of successful SQLi attacks by limiting the value of the extracted data. Researchers have also proposed using encryption techniques to combine user and password data used for authentication into a passkey. This method requires the selection of characters at random from the username and password, which would not only encrypt user input, but also ignore any injected code and prevent SQLi attempts from being executed.
Are other database servers (Oracle, MySQL, Sybase, DB2) subject to SQL injection?
Technically, SQLi applies only to SQL databases. Yet attackers can use the same methods on other databases. Attackers would change the specifics of the injection commands from SQL to the database commands appropriate for the database being attacked. Protection of these other databases uses the same techniques as used to prevent SQLi (user input sanitization, restrict database code, etc.).
Bottom Line: Prevent SQL Injections
Much like the spirit of zero trust security, mitigating SQL injection attacks means leaning into the reality that all user-submitted data could be malicious. Instead of giving attacks any leverage over SQL vulnerabilities, organizations should plan to review SQL security posture and application development.
Of course, SQLi will not be the only threat to the organization. While first database security steps like input validation, sanitization, prepared statements, and stored procedures are essential, they only solve one issue. Poorly written code exposes organizations to many other threats to check for including Cross-Site Scripting (XSS), buffer overflow, and other common vulnerabilities.
Organizations must also defend against related threats such as maintaining the hosting web and database servers, protecting the network, access management, security monitoring, and supply chain threats. After all, the website and database code vulnerable to SQLi is only one part of a larger and interrelated IT environment.
Read next: Database Security: 7 Best Practices & Tips
This updates an article by Aaron Weiss published on August 16, 2012. Updated by Paul Rubens on May 2, 2018, by Sam Ingalls on March 11, 2021, and by Chad Kime on May 16, 2023.
