intro
Injection attacks are always at the top of database threats and it’s safe to say that they will remain at the top of the pile for years to come. Learn how to prevent SQL injection attacks.
Injection. Once you hear this word, what associations does it bring? For many, this keyword associates with data breaches, for others — it reminds them of ways to fight against it. So, how to prevent SQL injection attacks?
In this blog, we tell you what injection is, how to protect against it, and what can you do to prevent it from striking in the first place.
What Is SQL Injection?
To find out how to prevent SQL injection, first, we must understand what SQL injection is and what it means.
Injection, in simple terms, stems from the failure of developers to validate user input before passing it on to a database. In other words, if you have write code like this, you have problems:
You have problems not only because your code has errors (you cannot use MySQLi and fetch results with PDO — you have to use one or the other to query the database and return results from it), but also because you pass user input straight into a database.
What Makes SQL Injection so Dangerous?
Passing user input straight into a database is dangerous because by doing so, you open the doors for attackers to submit malformed data. If an attacker submits a query like ";DROP TABLE demo_table;-- the query will then become SELECT * FROM demo_table WHERE details = "";DROP TABLE demo_table;-- and your database will:
DbVisualizer visualizes the resulting malicious query well:
There’s no need even to execute this query — everything’s as clear as water to begin with. One query ends, another drops the table, and the rest of the input is ignored.
Granted, a full understanding of how to prevent SQL injection also necessitates you to understand that SQL injection can be complex and that there are multiple types of SQL injection in the first place: most developers do know that writing code in such a way is a highway to disaster (the SQL injection payloads you will see in the wild will differ, some significantly), but combined sloppiness in your queries, code, and server development do open doors for injection regardless.
SQL Injection Attacks in the Real World
The SQL injection examples you will see in the wild will differ because no one attack is the same. Blind time-based SQL injection attacks will rely on the following factors:
Blind time-based attacks will rely on those three because they rely on an application not providing errors and a time-based factor. Other types of SQL injection can wiggle with the steps 1 and 2, however the third step will always be the same. That’s why before understanding how to prevent SQL injection you must first consider the types of SQL injection, too.
There are three — classic (user input passed straight into a database), time-based due to server configuration or other factors (see example above), and out-of-band when the “channels” between which an attacker sends and receives information differ.
How to Prevent SQL Injection?
This piece of code is not vulnerable to SQL injection:
This piece of code is not vulnerable to SQLi (short for SQL injection) because we don’t pass user input straight into a database and instead, bind a value separating it from the query. That’s all you need to do — the answer to the question how to prevent SQL injection is “don’t pass user input to the database.”
Avoid passing user input to the database by:
That’s it, really — not rocket science, is it? You can also use a mixed approach in that you can use data objects, whitelist input validation and minimize privileges, but be advised that each step towards security will likely have a drawback on something else (e.g. users may no longer be able to run queries like “Nissan Selecta” if you’re running a car sales website because it contains the select keyword which your application/input validation functions may interpret as malicious.) Using a firewall also helps — don’t rely on it, but if you can, implement a firewall into your application to block (and, even better, monitor intrusion attempts) so you can sleep soundly at night.
What to Consider When Preventing SQL Injection
Before looking into ways how to prevent SQL injection and looking at the downsides (most security solutions will have a drawback on user experience), consider:
After these things, also think about your use case from a user perspective once again:
Other Things to Consider
Finally, make use of simple things — use arrays for input validation, make use of roles (roles are collections of privileges), and consider using SQL clients for your use case.
SQL clients will not prevent your application from being exploited by SQL injection — they will, however, make you better understand your queries and the components within them.
Good SQL clients will let you take them for a test drive, too — grab a free 21-day trial of DbVisualizer by clicking on this link and explore its internals: with support for 50+ data sources, you will surely find your beloved database in the list.
Lastly, keep a cool head on your shoulders: no need to over-obsess over injection attacks (or any attacks for that matter) — do what you can and delegate some tasks to other applications/people. Chances are that a firewall for $10 a month will do a better job in terms of preventing attacks than one developer familiarizing himself with your code base. Do be wary and vigilant — and remember that simplicity is sometimes the best policy.
Summary
How to prevent SQL injection? That is a question on the top of mind of many developers, engineers, DBAs, and even security experts — the answer is “don’t pass user input to a database.” There are various ways to protect yourself from such attacks (PDO, firewalls, input validation, etc.), however, the best way will always be to avoid introducing such attacks into your database in the first place.
FAQ
How to prevent SQL injection?
Prevent SQL injection by using prepared statements, firewalls, input validation, and other measures that help ensure that user input is not acted on by your database.
Why is SQL injection so important?
Injection is important because injection attacks were, are, and presumably, will remain to be the top attack vector regardless of the application you’re building. Injection attacks are prevalent because they’re easy to come across, easy to exploit, and the rewards for attackers exploiting them are often huge enough to warrant the risk.
What resources do you recommend to brush up on database attacks and other interesting topics?
For those searching for ways to brush up on database internals and ways to improve performance, I’d recommend the book “Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case.” The book will not only walk you through the security measures you can implement into your database and application to prevent injection attacks but also tell you what makes your database performance dive and how to optimize it for the future.
