In this article we are presented with few methods and tips to prevent SQL Injection attacks in our web application.
In simple term when an application takes user data as an input, there is an opportunity for a malicious user to enter carefully crafted data that causes the input to be interpreted as part of a SQL query instead of data in our application. Those who are not familiar with SQL Injection attacks can read a detailed information about it in my previous article.
Now let’s focus how can we prevent prevent SQL Injection attacks:
1. Use prepared statements and parameterized queries.
These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.
We have two options to achieve this:
(i) Using PDO
1 2 3 4 5 6 7 | $stmt = $pdo->prepare('SELECT * FROM products WHERE product = :product'); $stmt->execute(array('product' => $product)); foreach ($stmt as $row) { // do something with $row } |
Note that real prepared statements are not used by default when using PDO to access a MySQL database. To fix this you have to disable the emulation of prepared statements as an example given below:
1 2 3 4 | $dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass'); $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
The first setAttribute() line is mandatory which tells PDO to disable emulated prepared statements and use real prepared statements. The second setAttribute() line, the error mode isn’t strictly necessary, but it is advised to add it.
Read more about PDO
(ii) Using MySQLi
1 2 3 4 5 6 7 8 9 | $stmt = $dbConnection->prepare('SELECT * FROM products WHERE product = ?'); $stmt->bind_param('s', $product); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do something with $row } |
The parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn’t intend.
Read more about MySQLi
prepared statements give you some speed gains as it will only be parsed and compiled once if you execute the same statement many times in the same session.
2. Validate and Sanitize
User input validation and proper sanitization is an important key to prevent SQL Injection attacks. For example: use mysqli_real_escape_string() to sanitize input.
3. Remove unused plugins & scripts
Timely clean up your application by removing plugins code and scripts you may have but not in use.
4. Avoid dynamic SQL
Don’t use dynamic SQL when it can be avoided. Use prepared statements, parameterized queries or stored procedures instead whenever possible.
5. Adopt MVC pattern/framework or CMS
Adopting the MVC pattern and a framework as well as CMS like Laravel or WordPress is probably the right way to go. Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks and CMS.
6. Use appropriate privileges
It is far safer to use a limited access account to your database, and can limit what a hacker is able to do. Don’t connect to your database using an account with admin-level privileges unless there is some compelling reason to do so.
7. Turn debugging off in production
Hackers can learn a great deal about database architecture from error messages, so to display minimal information, always ensure that an external visitor gets nothing more than the fact that his actions resulted in an unhandled error.
For example: for WordPress, keep WP_DEBUG as true in development environment only and keep it false in production to display detailed error messages on the local machine only. You can use the “RemoteOnly” customErrors mode in .NET (or equivalent) to achieve the same.
8. Update and patch
Vulnerabilities in applications and databases that hackers can exploit using SQL injection are regularly discovered, so it’s vital to apply patches and updates as soon as practical.
9. Limit failure attempts
It’s wise to take user away from your input page after a certain numbers of continuous invalid inputs. It will not only defend your application from SQL Injections but also from those who try to guess sensitive data belonging to another user.
A common example we see in practice, is to allow user to have only 3 or 5 login attempts at max.
10. Last but not least
Below are few more tips to to prevent SQL Injection attacks.
- Consider a web application firewall (WAF) either software or appliance based to help filter out malicious data.
- Reduce your attack surface through getting rid of any database functionality that you don’t need to prevent a hacker taking advantage of it.
- Keep your secrets secret always and don’t forget the basics like change the passwords of application accounts into the database regularly.