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:

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:

  1. Filter database inputs: Detect and filter out malicious code from user inputs
  2. Restrict database code: Prevent unintended database queries and exploration by limiting database procedures and code.
  3. Restrict database access: Prevent unauthorized data access, exfiltration, or deletion through access control restrictions.
  4. Maintain applications and databases: Keep databases fully patched and updated. Upgrade when possible.
  5. 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.

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.

Also Read: Are Your Databases Secure? Think Again

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: IBM AppScan Takes Aim at Input Validation

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.

Also Read: Firewalls as a Service (FWaaS): The Future of Network Firewalls?

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.

Read Also: Top Privileged Access Management (PAM) Software

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 to limit the usefulness of any extracted data.

Also Read: Best Encryption Tools & Software for 2021

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

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

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 Monitoring (SIEM) tools.

Also Read: Best SIEM Tools & Software for 2021

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.

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

Also Read:

Check out this course on Ethical Hacking-SQL Injection Attack.

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:

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.

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 December 27, 2022.

Sam Ingalls
Sam Ingalls
Sam Ingalls is an award-winning writer and researcher covering enterprise technology, cybersecurity, data centers, and IT trends, for eSecurity Planet, Tech Republic, ServerWatch, Webopedia, and Channel Insider.

Latest articles

Top Cybersecurity Companies

Related articles