Best practices for preventing a SQL injection attack
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in September 2012
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.
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.
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.
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation