Dynamic SQL: The Double-Edged Sword — How to Harness its Power Without Getting Stabbed by SQL Injection

We tend to love dynamic things for the flexibility and performance they give us; as discussed in my previous article (check here for it), dynamic SQL allows us to create SQL statements on the fly based on the user inputs or other dynamic factors and can be optimized for specific data being queried (useful when we work with large datasets or complex queries).

“Hmm…it’s too good to be true” you might think and you’re right. Besides all the benefits we can have from using it, we’re still forgetting the other edge of the sword — SQL injection 😨

🗡Before fighting, let’s study a little about our biggest enemy — SQL injection

Our enemy has the power to inject and execute malicious SQL statements within a web application’s database. His evil intentions reach the surface when he gained the ability to insert SQL commands into our database using a web form input or a URL query parameter, which passes through the web application’s backend directly into the database.

There are many types of SQL injection, but the most known are:

1. Union-based SQL injection — it’s the most popular one and uses the UNION statement, and by using this the bad guy tries to obtain information from multiple tables.
2. Error-based SQL injection — SQL errors are used to extract valuable information from the database. The attacker inserts a specially crafted SQL statement that causes the database to generate errors, and along with those errors, some information about the database’s schema or data may be revealed.
3. Blind SQL injection — using this type of injection, our villain is more interested in the behavior of the application or database server; he/she collects precious information about how an application or a web page loads to see how much time the SQL server takes to process the SQL query they passed in the user input.

The ways of the attacks may be:

• a simple login form or any other form input — usually attackers use the web application inputs to inject malicious SQL code by typing specific characters or commands. If this code is accepted by the application without sanitizing the input values, our database is in real danger.
• HTTP request — the HTTP request can be manipulated by a malicious user, so in this way, the attacker can retrieve unauthorized data.
• File upload — attackers can inject SQL code by uploading files that contain code that is executed when the file is accessed (when our web application has the file upload option)

🛡 Now that we know that, let’s prepare our defense

When it comes to databases, the most used and recommended ways to build your fort are:

• limit the user privileges — by limiting the privileges of database users, you reduce the likelihood that an attacker will succeed in their plan and gain access to your most sensitive data.
• stored procedures — if you are a SQL developer try to convince your programming teammates to use stored procedures (and even help them by creating them) instead of SQL statements directly in code.
• parametrized queries — use parameters to avoid concatenation of your SQL statement with user input, a situation that will allow the attacker to exploit your vulnerabilities by inserting malicious SQL code into the input.

I work mainly with SQL Server and I’m a big fan of using dynamic SQL, of course when it represents the best solution for a problem, I always use “sp_executesql”.

sp_executesql — is a system stored procedure in Microsoft SQL Server that allows the execution of dynamically generated SQL statements.

I had a situation when my team need a stored procedure to retrieve sensitive data from different tables on different pages of a web application; my first thought was to use dynamic SQL and below is the logic I also used in my stored procedure:

1. I declared a variable that will contain the value based on which we want to display the data

declare @UserEmail nvarchar(150), @sql nvarchar(max);

2. I’ve built the required SQL statement that will return the necessary data based on the condition in the WHERE clause

set @sql = N'SELECT CustomerID, FirstName, LastName, EmailAddress 
             FROM Customer
             WHERE EmailAddress = @email';

3. Executed with sp_executesql the dynamic SQL statement

EXEC sp_executesql @sql, N'@email nvarchar(150)', @email=@UserEmail;

So when sp_executesql runs, it will automatically substitute the value of `@email` into the query and then will execute and return the needed information about the clients whose email address matches the pattern received from the `@UserEmail` variable.

Don’t forget!

Be that knight in shining armor for your database and win the fight without getting hurt by your sword.

Have fun and stay tuned for more articles 😸

If you liked my articles, let’s spend our coffee break together here 😉. Thank you for your support!