Which of the following are the best ways to protect against injection attacks?

Which of the following is the best strategy to prevent SQL injection attacks against a web application? (Source: Wentz QOTD)
A. Implement form-based authentication using the POST HTTP method
B. Employ an application framework that supports parameterized queries
C. Accept TLS/SSL connections only
D. Verify if the injections are automated by a robot

Kindly be reminded that the suggested answer is for your reference only. It doesn’t matter whether you have the right or wrong answer. What really matters is your reasoning process and justifications.

My suggested answer is B. Employ an application framework that supports parameterized queries.

• Form-based authentication using the POST HTTP method is a common way to send username and password to the backend server for authentication. It doesn’t help.
• TLS/SSL encrypts the malicious SQL code as normal data. It doesn’t help.
• After clicking the “I’m not a robot” icon, the attacker can go on typing in malicious SQL code. It doesn’t help.

Mitigations

• The best way to prevent SQL injection is to validate user inputs. All invalid characters are not allowed.
• Parameterized queries don’t really validate user inputs; it just treats data as data so that raw data won’t become SQL queries.

SQL injection

SQL injection occurs when a programmer concatenates strings to assemble SQL instructions. Strings are plain data, while SQL instructions are executable code.

1. Users can type in snippets or fragments of SQL code as inputs in an HTML form, which will be posted to the back-end server for processing.
2. If the programmer treats the SQL code posted from the attacker as ordinary data and combines them with the primary SQL instructions, the assembled SQL code could be executed successfully. That’s how SQL injection works.

SQL Query Assembled from Strings

The following is an example that combines strings into a SQL query without the employment of SQL parameters. The user inputs, $email and $password are expanded to raw strings (depicted as the following text in red) and combined into a SQL query.

SELECT * FROM users WHERE email = 'xxx@ xxx.xxx' AND password = md5('xxx') OR 1 = 1-- ]');

Parameterized Query Example

@Age is a parameter used in the SQL query, @SqlInstruction.

DECLARE @SqlInstruction NVARCHAR(500);
SET @SqlInstruction = N'SELECT * FROM Users WHERE Age = @Age;';
EXEC sp_executesql @SqlInstruction , N'@Age INT', 99;

References

• Role-based Access Control vs Attribute-based Access Control: How to Choose
• RBAC vs. ABAC Access Control Models: What’s the Difference?
• SQL Injection Example – Parameterized Queries – Hindi

以下哪項是防止對Web應用程序進行SQL注入攻擊的最佳策略？
A. 使用POST HTTP方法實現基於表單的身份驗證
B. 使用支持參數化查詢的應用程序框架
C. 僅接受TLS/SSL連接
D. 驗證機器人是否自動執行注射

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.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.