How to Prevent SQL Injection: 5 Key Methods
Most modern websites and applications connect to databases and the databases are 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 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.
For related resources, see also:
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:
Each method can be accomplished through various techniques that we explore in more detail below.
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.
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:
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.
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:
One the database processes the attacker’s string, the database will see the command:
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:
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:
While typecasting can be very useful, it is more limited in application and will not be as commonly used.
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.
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.
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.
Also Read: Are Your Databases Secure? Think Again
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: IBM AppScan Takes Aim at Input Validation
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 whitlisting, user input sanitization, etc.) must be used.
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:
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.
Also Read: Firewalls as a Service (FWaaS): The Future of Network Firewalls?
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.
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.
Read Also: Top Privileged Access Management (PAM) Software
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 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 to limit the usefulness of any extracted data.
Also Read: Best Encryption Tools & Software for 2021
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 should have minimal access to the target server and access is limited strictly to mission-critical data. Linked servers should have distinct logins from any process on the target server.
Also Read: Cloud Bucket Vulnerability Management in 2021
Vulnerabilities in applications and databases that are exploitable using SQL injection are regularly discovered and publicly identified. Organizations must stay current with cybersecurity 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.
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 Monitoring (SIEM) tools.
Also Read: Best SIEM Tools & Software for 2021
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.
There are several free or commercial penetration tools an organization can use to identify potential SQL injection vulnerabilities.
Typically, these 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.
Check out this course on Ethical Hacking-SQL Injection Attack.
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:
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.
This updates an article written on August 16, 2012 article written by Aaron Weiss, updated by Paul Rubens on May 2, 2018, updated by Sam Ingalls on March 11, 2021, and updated by Chad Kime on December 27, 2022
Top Cybersecurity Companies
See full list
eSecurity Planet is a leading resource for IT professionals at large enterprises who are actively researching cybersecurity vendors and latest trends. eSecurity Planet focuses on providing instruction for how to approach common security challenges, as well as informational deep-dives about advanced cybersecurity topics.
Advertise with TechnologyAdvice on eSecurity Planet and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.