8 best practices to prevent SQL Injection Attacks

If you’re new to SQL Injection Attack, visit SQL Injection Attack explained, with example.

To keep your database safe from the SQL Injection Attacks, you can apply some of these main prevention methods:

1. Using Prepared Statements (with Parameterized Queries)

Using Prepared Statements is one of the best ways to prevent SQL injection. It’s also simple to write and easier to understand than dynamic SQL queries.

This is where the SQL Command uses a parameter instead of inserting the values directly into the command, thus prevent the backend from running malicious queries that are harmful to the database. So if the user entered 12345 or 1=1 as the input, the parameterized query would search in the table for a match with the entire string 12345 or 1=1.

Language specific recommendations:

Java EE – use PreparedStatement() with bind variables
.NET – use parameterized queries like SqlCommand() or OleDbCommand() with bind variables
PHP – use PDO with strongly typed parameterized queries
Hibernate - use createQuery() with bind variables (called named parameters in Hibernate)
SQLite - use sqlite3_prepare() to create a statement object

For example, using prepared statement in PHP:

$stmt = $dbh->prepare('SELECT * FROM customers WHERE ssn = :ssn');
$stmt-> bindParam(':ssn' => $ssn);

For further reading, you can visit Query Parameterization Cheat Sheet.

2. Using Stored Procedures

Stored Procedures adds an extra security layer to your database beside using Prepared Statements. It performs the escaping required so that the app treats input as data to be operated on rather than SQL code to be executed.

The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is written and stored in the database server, and then called from the web app.

If user access to the database is only ever permitted via stored procedures, permission for users to directly access data doesn’t need to be explicitly granted on any database table. This way, your database is still safe.

3. Validating user input

Even when you are using Prepared Statements, you should do an input validation first to make sure the value is of the accepted type, length, format, etc. Only the input which passed the validation can be processed to the database. It’s like checking who is at the door of your house before you open it and let them in.

But remember, this method can only stop the most trivial attacks, it does not fix the underlying vulnerability.

4. Limiting privileges

Don’t connect to your database using an account with root access unless required because the attackers might have access to the entire system. Therefore, it’s best to use an account with limited privileges to limit the scope of damages in case of SQL Injection.

5. Hidding info from the error message

Error messages are useful for attackers to learn more about your database architecture, so be sure that you show only the necessary information. It’s better to show a generic error message telling something goes wrong and encourage users to contact the technical support team in case the problem persists.

6. Updating your system

SQL injection vulnerability is a frequent programming error and it’s discovered regularly, so it’s vital to apply patches and updates your system to the most up-to-date version as you can, especially for your SQL Server.

7. Keeping database credentials separate and encrypted

If you are considering where to store your database credentials, also consider how much damaging it can be if it falls into the wrong hands. So always store your database credentials in a separate file and encrypt it securely to make sure that the attackers can’t benefit much.

Also, don’t store sensitive data if you don’t need it and delete information when it’s no longer in use.

8. Disabling shell and any other functionalities you don’t need

Shell access could be very useful indeed for a hacker. That’s why you should turn it off if possible. Remove or disable all functionalities that you don’t need too.

Final thought

The key to avoiding being the victim of the next SQL Injection Attack is always be cautious and trust nobody. You don’t know when the bad guy is coming so hope for the best and prepare for the worst, validate and sanitize all user interactions.

TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 