Manage Learn to apply best practices and optimize your operations.

Best practices for preventing a SQL injection attack

One of the sneakiest ways for a hacker to invade your system is via a SQL injection attack. Expert Robert Sheldon explains how to keep hackers out.

A SQL injection attack continues to be one of the biggest threats to SQL Server. A database can be susceptible...

to such attacks if the database -- or the front-end application it supports -- contains code that a hacker can exploit by injecting rogue SQL into one of the applications' data input fields. The application then submits the modified code to the database, which can result in the hacker being able to access sensitive information, modify data or database objects, and run administrative operations against the database or, in some cases, against the host operating system.

To protect their databases, SQL Server and application developers should follow a set of best practices that help mitigate the threat of SQL injection. For both SQL Server and application developers, the primary goal is isolating the SQL command language from user input as much as possible.

An introduction to SQL injection

SQL injection attacks occur when application or database code dynamically generates queries that concatenate command language with user input. The user enters information through the application interface that becomes part of the SQL statement executed against the database. Let's look at an example: Figure 1 shows a simple web-based application with a single user input field.

Figure 1. Entering a user ID into the user interface

The application returns a list of movies that the user has rented. The user enters an account ID and clicks Enter. The application or database concatenates that ID with a predefined SELECT statement so that the ID becomes part of the statement's WHERE clause. If the user provides a legitimate ID, a list of movies is returned to the interface and everyone is happy.

Now suppose a hacker wants to break into the system to retrieve data or do damage. For instance, the hacker might try to delete all the data from the rentals table (assuming the hacker is able to acquire the name of the table through a separate hack). Instead of providing a simple user ID, the hacker might also add a TRUNCATE TABLE statement, as shown in Figure 2.

Figure 2. Entering rogue code into a user interface

Notice that the value entered into the user interface now includes a semicolon, followed by the TRUNCATE TABLE statement. Because SQL Server supports the semicolon as a way to terminate a statement and supports multiple statements in a batch, the hacker can easily send those statements to the database. As a result, all data will be deleted from the rentals table.

This, of course, is a very simple scenario, but it illustrates the logic behind SQL injection attacks. The trick is to make sure this can't happen, which is why it can help to have guidelines on what and what not to do.

Best practices for preventing SQL injection attacks

In an ideal world, the database team has full control of how an application is permitted to access the database. For instance, the team might insist that all queries generated by an application be parameterized or that the application access the database only through stored procedures, leaving the application to call those procedures rather than issuing its own SQL statements.

But the reality of application development and maintenance can make such pronouncements difficult to enforce, particularly when dealing with legacy systems, mixed environments and colliding opinions. Even so, the database developer must remain vigilant to protect against SQL injection attacks. For this reason, we've come up with a set of best practices that you should consider when creating database components to support various types of applications. Not all of these suggestions will fit every scenario and some might be only partially applicable, but each one is worth a closer look. From there, you can apply what best fits your needs and situation.

Use stored procedures

When possible, permit applications to interact with the database only through stored procedures. That way, the database account used by the application requires only the permissions necessary to execute the stored procedure, without needing permissions to access the underlying tables. Even if the application code is susceptible to SQL injection attacks, those attacks will fail because the application lacks the permissions necessary to access or manipulate those tables. Plus, stored procedures type-check input parameters, which can help mitigate an attack if a hacker tries to inject a value that violates the type.

Despite these advantages, stored procedures alone are not enough to ward off SQL injections. They should be part of an overall security strategy against such attacks. However, some stored procedures are safer to implement than others. For example, static stored procedures don't take parameters and therefore can't be injected with rogue code. Stored procedures that contain only parameterized SQL are also resistant to SQL injection because the data language is kept separate from the command language. In other words, avoid dynamic SQL within your stored procedures whenever possible.

Use dynamic SQL only when you can't avoid it

Dynamic SQL can greatly increase your risk of SQL injection attacks when the command language is concatenated with user input. In some cases, however, it's not possible to avoid dynamic SQL. For example, you might want to define a stored procedure that creates a database login, with the login name passed in as a parameter. The problem is that the CREATE LOGIN statement doesn't accept a variable value for a login name, so you have to build your statement dynamically in order to pass in the name.

More on SQL injection attacks

How susceptible is your site to a  SQL injection attack?

Check out these free tools to avoid SQL injection attacks

Malicious injections aren't just for SQL anymore

One way you can help mitigate the risks associated with dynamic SQL is by properly escaping all user input. Escaping user-supplied values helps to render special characters harmless that can be passed in with that input, such as brackets or single quotes. These characters, when used with other language elements, can pose a threat to the database when concatenated with the static part of the SQL statement. To escape these characters, use the QUOTENAME or REPLACE function as appropriate to handle identifiers and string values. When using either function, be sure to properly calculate the buffer length to allow for the escaped characters or you'll be opening yourself up to truncation attacks, a type of SQL injection attack that uses truncation to inject rogue code.

Another method you can use to prevent SQL injection attacks is to use the sp_executesql system-stored procedure to execute your dynamic SQL, rather than a simple EXCECUTE statement. By using the sp_executesql stored procedure, you have a way to parameterize the dynamic SQL so the data remains separate from the command language. But be sure to use the stored procedure properly -- don't use it simply as a replacement for EXECUTE. You must include the parameters in the SQL statement when you create it and again when you call the sp_executesql stored procedure.

Use the principle of least access when granting database access

Even if you can limit application access to stored procedures and avoid using dynamic SQL, you must still ensure that you're restricting database access to the fullest degree. One of your best defenses is to follow the strategy of least privilege. Every database account should be assigned the least privileges necessary to access the database. That is why restricting access to stored procedures can be so effective. Ideally, you grant execute access to the procedures and permit no access to anything else. If you do permit an application to execute SQL statements directly, the application should use a database account with the least privileges necessary, with specific controls over exactly what that account can read and modify. Under no circumstance should you assign an administrative account (such as sysadmin) to an application.

Even within a stored procedure, you should follow the strategy of least access. For example, if you use the EXECUTE AS clause to run the SQL statements in the procedure, specify an account with only minimal privileges. If a high-privilege operation must be performed, create a stored procedure to perform that operation and sign the stored procedure with a certificate. The goal is to ensure that even if an attacker were to discover a security hole in the application, there would be little they could do. Applications that access the database should always be limited to a low-privileged account that only has the minimum rights required to execute the statements it's permitted to submit to the database.

Use testing and monitoring to guard against SQL injection

No matter how careful you've been in protecting against SQL injection attacks, you should still run your database code through the necessary checks to make sure it's safe. That begins with a code review that looks specifically for issues related to SQL injection susceptibilities such as improper dynamic SQL. From there, you can move on to the QA phase. In addition to any normal testing and verification processes that would be performed, the database, along with the application it supports, should be subjected to SQL injection attempts to try to find vulnerabilities. The testing process should include all levels of SQL injection attacks, including those that attempt to truncate data. Finally, once the database has gone live, log files and other tracking devices should be used to monitor the database for any indication of SQL injections.

Protecting against SQL injection attacks

The guidelines suggested above only touch upon the underlying topics, but they should point you in the right direction. Given the proliferation of mobile apps and the growing need to synchronize data among multiple devices, the need for vigilance is greater than ever. It takes only a single vulnerability to put your entire database at risk, and database security is no place to take shortcuts. Database developers and application developers must work together to avoid such vulnerabilities. SQL injection attacks can be prevented, but only if you take the steps necessary to protect your system.

Dig Deeper on SQL Server Security