How To Prevent SQL Injection Without Prepared Statements
Avoid hackers adding a malicious SQL query into your parameter
SQL injection is an attack where a hacker adds a malicious SQL query into your parameter, which is then run on the SQL server.
This can have a fatal effect on your application, as the attacker has full control over your data source. This mostly happens when you pass variables to the query with simple concatenating strings to build the final query.
To prevent this from happening, we can use prepared statements that send variables separately so they can be treated as one string and any query in it will not be executed on the database.
So far so good. But what if you cannot use them?
I will tell you a short story that occurred during a project I worked on. We had a big REST API that communicated with a database.
During maintenance, we discovered that some developers wrote dynamic queries that only passed input variables from the body parameters into the query.
We thought: “This is the end, execute evacuation plan.”
Well, after we calmed down a little we realized that these routes were blocked by a firewall and were not accessible from the outside world. But, of course, after a deeper look, we found some that were not blacklisted.
So, we had to create an ultra urgent top priority ticket to fix these vulnerabilities and get rid of the dynamic query hell. You may have guessed that it was me who had the pleasure of working on this ticket.
I started to work on it. I created a big red warning saying: “This site has SQL-injection vulnerabilities. We kindly ask you to respect it and not to break it.” Just kidding.
I found out that, thankfully, most of the parameters were numbers or dates so they did not cause any trouble.
parseFloat, or date processing with moment, before we sent it to the query.
When you process variables with a validation, parsing, or processing function before inserting it into the query you can sleep well, because if any SQL injection comes there, it should invalidate the value and an error should be thrown.
You can build on this also for string inputs. I found some string variables that were kind of secure. We had a check on the length of the string. And this really makes life harder for attackers because if you have to write injection in 20 characters, it is harder than in 100.
But still, DROP users — it is the limit and can cause unspeakable damage. So, length check is not really a solution unless it is max three characters. But, you cannot do this for dynamic values for ordering or tables.
There is something that you can do. For example, if you want to inject a field for order by or table, you know, before injecting it, which values to expect. If you know that the table has the field’s ID and name, you should not permit that the variable has anything else then these two values.
This is quite easy to check and makes your injection secure because the injected value is the only value that is allowed. You can event connect it with some selects on the db to make it more robust.
But, there sure were vulnerabilities in our codebase where we inject string variables that could not be checked against a list.
So, I started rewriting these calls to use prepared statements and was checking git annotations to see who was so silly to use dynamic queries here and create this mess.
As you can imagine, this was a high prio so we created an ad-hoc test release with fixes and then the real hell started. Nothing was working and we were getting tons of errors that the protocol we used for communication with the db could only send 2100 parameters.
Hmm… so those guys were not that silly after all.
You probably think: “Well, these db libraries used to have some sanitizing method, that will make it fine.” Yes, they do. However not the one we used.
If you already have a massive application built, you’ll have to live with it. But, if you have the resources, you should check if you can change your stack to not have such limitations.
We tried to think of how we could solve this issue and reduce the need for the number of parameters but we soon discovered that the functionality we wanted to achieve simply needed that much.
So, I was back at the starting point. The only solution that came to my mind at that time was to create middleware that checks all inputs that come to the API against a regexp that will check SQL injection patterns.
When searching npm packages, I found out I was not the only one that wanted to do this so I started testing some.
As you can imagine, every one of them checked different SQL injection types at different levels. So, I ended up with custom middleware with a custom set of regexes that matched all possible test cases that I found as possible SQL injection patterns.
We took this proposal to test again. And soon, we again got loads of errors.
It turned out my regexes were too strict and were saying that valid (at least from a business perspective way) strings were injections.
In the end, we came up with a white list that was easily configurable. And, if any process throws an SQL injection alert and we saw that this value was correct, we could easily add it to the whitelist and rerun the service.
This solution was accepted as okayish and it seems to work fine. We had about four routes that didn’t use prepared statements from a few thousand, so hackers would need to invest quite an effort to find them.
And, even after that, there is the check for SQL injection strings and all fields have restricted length to make hacking a bit harder again.
There are some third-party platforms that can do what we did. For example, Sqreen can be connected to your application and it will automatically block any SQL injection requests.
I am not sure how effective this tool is; I did not get a green flag for this implementation from the customer. But, if you really need to make a vulnerable application safe from SQL injection, you should definitely try an application like that.
So, what to do to make your applications secure?
- Make sure you use prepared statements in every possible way.
- If it is not possible, prefer to inject preprocessed values (numbers, dates).
- If a string needs to be inserted, check if you can validate it against a predefined list.
- If none of the above can be applied but your library has a sanitizing method, use it.
- At least try to validate the parameters with regexes or use a third-party application to validate inputs.
I’d also recommend the last step if you have everything safe under prepared statements.
You can get good statistics if your app was under SQL injection attack and what the attackers tried to obtain. Maybe you can take action against them (maybe ban the IP).
I bet there are a lot of developers that can see their apps in my example.
If you do, you should really take action because there is a dark world out there. And, if you did not see yourself in that example and feel safe, just re-check that everything is safe to be sure. You never know…
Hope you enjoyed this article.