How To Prevent SQL Injections
SQL injections are arguably the most common type of web attack to steal sensitive data from organizations. Any time you hear about data breaches resulting in stolen passwords or credit card data, it’s often the result of an SQL injection.
This in-depth guide is designed to help web administrators and website owners prevent SQL injections before an attack. Follow the steps below to learn how to prevent SQL injections and keep your company’s data safe.
Step #1: Validate User Inputs
One of the first things you should do is validate all user inputs. The process is commonly referred to as a “query redesign” or “input validation.”
You’ll need to identify your most essential SQLs and create a whitelist for valid SQL statements. Then leave all unvalidated statements out of your queries.
You can also set up the user inputs as they relate to different contexts. For example, let’s say you have a form field on your website for visitors to input their email addresses. This field could be set up to only allow submissions with an “@” symbol, which could filter out someone from injecting something malicious into this field.
For fields asking for a social security number or phone number, you can limit those to only accept submissions with nine digits and ten digits, respectively.
This step alone won’t stop an SQL attack, but it’s a basic website security step that you can take to prevent common SQL injections.
Step #2: Data Sanitization
Next, you should do a data audit and clean up your data that contains special characters.
It’s common for attackers using SQL injections to use sequences of unique characters to exploit unsanitized databases. But if you set up your database to deny string concentrations, you can prevent these attacks.
This takes your input validation one step further. If you limit special characters in your data, it ensures that dangerous special characters from an SQL injection won’t get passed into an SQL query with instructions.
Using prepared statements is an excellent way to prevent unauthenticated queries in your SQL databases. But it starts with data sanitization, specifically by limiting special characters.
Step #3: Actively Track Updates and Patching
Attackers have a knack for finding vulnerabilities in different databases and web applications. This turns into a game of cat and mouse.
Once an application realizes that something in its system is exploitable for SQL injections, they need to patch the vulnerability. But if you’re not keeping track of these and don’t update applications, software, or plugins, then your site will still be susceptible to those attacks.
It’s crucial that you keep a close eye on your plugins, frameworks, server software, libraries, and everything else tied to your website. If there’s a new version, make sure you update it ASAP. Many times you’ll get a notification for updates, but other times you’ll need to do some digging.
Depending on the size of your organization and the number of programs it uses, you might need to consider investing in a patch management tool.
Step #4: Add Stored Procedures to Your Database
Stored procedures use variable binding to mitigate SQL injections. These procedures live in the database and connect to web applications.
While this won’t make your system completely impenetrable for SQL injections, it definitely helps. With that said, dynamic SQL generation can still bypass stored procedures.
So you should also harden your applications and operating system when you’re going through this step. Again, it’s not completely foolproof. But this extra effort makes it a little more challenging for SQL attacks to penetrate your hardened systems.
Step #5: Leverage Parameterization and Prepared Statements
Similar to stored procedures, parameterization and prepared statements use variable binding to write database queries.
If you take the time to define all of the SQL code in each query, you’ll be able to tell the difference between code and user input.
Many web administrators use dynamic SQL coding techniques for added flexibility with application development. However, dynamic SQL could make databases treat certain code as instructions. So if malicious code is executed via an SQL injection, it could be a problem.
But if you stick with standard SQLs, databases won’t treat malicious statements as commands. Instead, they’ll just get inputted as data—limiting the potential impact of the injection.
Step #6: Use Firewalls
I highly recommend using a WAF (web application firewall). An appliance-based WAF is an excellent and easy way to filter malicious data from your site.
Many of the best WAF solutions on the market today have default systems in place that prevent SQL injections. You can also configure these tools to meet your specific needs.
Refer back to step 3 on patching. Your WAF can potentially double as a solution to help you with patching and update management.
These commonly come in two different forms.
- NGFW — Next-generation firewall
- FWaaS — Firewall-as-a-service
Some of the modern FWaaS solutions come with next-generation functionality. It’s also worth noting that NFGW can be hosted in the cloud as well, meaning it doubles as FWaaS.
ModSecurity is a popular free and open-source WAF. It has SQL injection prevention capabilities that detect and filter potentially malicious web requests.
Step #7: Create User Privileges and Tighten Up Access Controls
It’s in your best interest to create and enforce the policy of least privilege. Think about what would happen if your SQL databases were compromised. You can reduce these risks with these types of access policies.
That’s why we previously discussed the importance of using select statements for your databases. You shouldn’t also have privileges to update, insert, or delete items. These make your system more susceptible to malicious commands via SQL injections.
With an access control policy, set up your database so that only users with administrative-level permissions can gain access. All other users should be automatically denied.
This way, if another user in your organization without admin-level access is compromised by an SQL attack, the attacker’s access will be limited.
You can take this one step further by configuring your database for read-only access. So if certain users without admin-level permissions need to use a database, they won’t have the ability to edit anything.
Again, if an attacker gains access to these types of credentials using an SQL injection, the database can’t be altered if the access is read-only.
Enforcing password policies is another way to harden your access controls. Beyond restricting access by user privileges, you can also reduce the chances of a breach by forcing users in your organization to protect their accounts.
Any default passwords should be changed immediately. You can also enforce password policies with minimum character lengths and complex character requirements for every account type. Set user passwords to expire on a regular basis, and force your team to continually update their accounts according to these policies.
Step #8: Eliminate User Accounts and Shared Databases
If multiple applications or websites are using the same shared database, it can be a disaster if you fall victim to a successful SQL injection. The same holds true for user accounts with access to multiple web apps.
While shared access might make things easier to complete tasks within your organization, it adds a significant risk for attacks.
Instead, make sure any connected servers have the least amount of access to the target server. Make sure these databases are structured, so they only have access to mission-critical information.
Each server linked should have its own unique login and account from other processes on your target server.
Step #9: Limit Information in Errors and Encryption Policies
This is a big mistake that I see web administrators make all of the time: They divulge way too much information in error messages.
Attackers can figure out a ton of information about your database architecture simply by reading your error messages. By only including minimal information, you can prevent the unintentional exposure of your architecture secrets.
The best way to do this is with “RemoteOnly” customErrors or similar configurations. This means that a detailed error message will only be displayed on local machines that are verified and trusted. But if an external hacker attempts an SQL injection and gets an error, they’ll just see a basic error message that doesn’t display any further details.
This can help safeguard your account names, table names, internal database structure, and more—ultimately making it more difficult for an attacker trying to find weak links for an SQL injection.
Using the same logic and thought process, you should also ensure your encryption keys are kept private as well.
Encryption is an excellent way to protect sensitive data while it’s in motion or in storage. But it’s useless if you’re not protecting your cryptography keys.
Make sure you really establish proper encryption and hashing policies. Otherwise, hackers can get their hands on the keys required to decrypt sensitive data and deploy an SQL injection.
Step #10: Deny Extended URLs
Many SQL injection attackers send extended URLs with the intention of server failure during login. This has been well-documented as an exploitation technique that triggers stack-based buffer overflows—all from long URLs.
Lots of web servers today are built to process large requests, including requests larger than 4096 bytes. But your web server software might not place the context of these requests in your log files. What does this mean?
An attacker could go under the radar and undetected with an SQL injection using extended URLs. A simple solution for this is to limit all URLs to 2048 bytes. Everything else will be denied by default.
Common Problems When Preventing SQL Injections
The steps above will definitely put you on the right track to preventing SQL injections. With that said, it’s common for some people to run across stumbling blocks or pain points when they’re going through this process.
I’ve identified the most common problems for SQL injection prevention and explained the solutions below.
Problem #1: SQL Injection Detection
Let’s say you’ve taken all of the steps in this guide to prevent SQL injection attacks. Now what?
Like many hacks and malicious attacks, there is no way to 100% stop and prevent SQL injections. Even if you’re following all of the latest tips, security policies, and best practices, a newer or sophisticated injection can still penetrate your site and database.
But unlike other types of attacks, SQL injections aren’t always obvious. It’s not like ransomware, which clearly shows when there has been a breach.
The best way to overcome this problem is with regular scans. I recommend using web vulnerability scanners, like Acunetix.
Acunetix gives you 90% of the scan results in minutes, allowing you to quickly identify any SQL injections, as well as other potential vulnerabilities. It automatically prioritizes your highest-risk vulnerabilities, so you know exactly what to fix first.
Problem #2: Assessing Weak Points
Using a tool to scan for vulnerabilities is great. However, you’ll still be playing a bit of catch-up if an SQL injection is detected.
Obviously, you’d rather stop the injection before it occurs. But how can you identify areas where you’re most vulnerable to an attack?
Running self-imposed attacks against your website or application is a viable option. Common ways SQL injection methods for this include:
- In-band SQL Injections — It’s one of the most popular SQL injection attacks that can come in an error-based or union-based form. The error-based method tests to see which queries get error messages so attackers can identify the database structure. Union-based attacks use statements and send results to the graphical user interface.
- Inferential SQL Injections — Also known as blind SQL attacks, an attacker can send Boolean-based attacks or time-based attacks. The first method forces a web application to return true or false statements that they can decipher into specific payloads. The latter sends queries to the database with a specified time cadence. But both options force the attackers to decipher data one character at a time.
- Out-of-Band SQL Injections — These attacks aren’t as common. They rely on database servers making HTTP requests or DNS requests to deliver data to the hacker.
Rather than performing these attacks on your own, you can use penetration testing tools to assess weak points in your website or web applications.
Problem #3: Continuous Monitoring and Audits
Preventing SQL injections is just one of the dozens of steps an organization must take to protect data and enforce cybersecurity policies.
So it’s common for organizations to follow these steps but then let the process fall by the wayside. This is a problem.
Instead, make sure you set reminders and assign responsibility to IT security members to review audit logs on a regular basis. This can coincide with your password policy updates and software patching that we discussed earlier.
It’s also in your best interest to go back and re-do some of the steps in this guide on a regular basis. For example, your access control policies might change over time, especially as your team scales and you add new tools. So you’ll need to assess those policies and user privileges to see if you’re still enforcing the best least privilege principles.
