sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 503.914.6442

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

SQL Injection Primer

Issue 7 Number 1 January 2015

Every few years in this newsletter I like to bring up the subject of SQL Injection because it is so vitally important to anyone who is charged with securing a SQL database. In my consulting work, I still find DBA's who think they are fully protected, but are not.

In the past I have written several articles looking at SQL Injection from different angles. This month I am writing a quick refresher on SQL Injection. My thinking is that if it is short, more people will read it.

If you would prefer a deeper dive into the details you might want to read an earlier but more detailed article on the subject. If you have the time, you might even consider buying Justin Clarke's book on the subject.

I guarantee that if you are responsible for protecting your database against SQL Injection, Justin Clarke's book will scare the hell out of you.

Yesterday I spent an hour on the phone with a caller who had been hacked by a very common exploit that pastes a malicious url all over the web site. After he cleaned up the damage, it was hacked again. I routed him to another of my articles that describes how to minimize the damage from an attack like this.

Here are a couple of common misperceptions surrounding SQL Injection :

Myth 1

If I use stored procedures and send all user-supplied input to the stored procedure as parameters, my database is safe from SQL Injection.

Not necessarily. It depends on how you use the parameters. If those parameters are used internally to build a command string that is then executed , any injected code will also execute. Whenever you execute a command string built from unsanitized user input, whether from ad hoc sql or from a stored procedure, your database is at risk. Stored procedures only protect you if you use the supplied parameters as parameters and not just as text strings that are concatenated into a SQL command.

Myth 2

If I sanitize the input from web forms and other places where users can enter names, comments or other text, my database is safe from injection.

While sanitizing user-input text is important, this is not the only place where malicious code is injected. Often, if not most of the time, a web application attack is focused on urls rather than forms. For example, you might have a page that does not allow a user to enter actual text but to select a product from a list. The application passes the ProductID back to the application in a url that might look like this:


All serious hackers have proxy tools that make it a simple matter to inject malicious code into this url following the parameter value. If the parameter value is used to build a sql statement for execution, the malicious code will execute.

Myth 3

Oracle databases aren’t vulnerable to SQL Injection

This myth probably exists because Oracle’s brand of SQL ( PL-SQL) does not allow more that one command per line. This does eliminate one form of injection called command stacking, however attackers have many other ways to inject malicious code into Oracle. It is fair to say that it is a little more difficult to hack Oracle than SQL Server, but it is far from impossible.

SQL Injection Defenses

The first line of defense against SQL injection is to follow the principle of least privilege in assigning permissions. You should have a dedicated login that handles all communication between the application and the database. That login should have the minimum permissions that will allow it to do only what is necessary. You can also reduce the privileges that are necessary in a number of ways. For example, all objects in the database can be owned by a schema in which the login application only has permissions to execute stored procedures or access views.

In any case, you should explicitly deny permissions on system tables like sys.objects, sys.columns and the associated views that a hacker can use to explore the schema.

The second line of defense is to write safe code. Avoid dynamic SQL in both ad hoc queries and inside of stored procedures or prepared SQL. Define the SQL code first, and then pass in the parameters afterwards. This can be done by using parameterized queries or using stored procedures.

Black Listing and White Listing

Parameterization works well for new applications and new databases. But it is often difficult or impossible to rewrite large amounts of code in legacy applications.

If you are saddled with a lot of dynamic sql in legacy code, the best defense might be to filter out malicious input before it gets to the database. The most effective way of doing this is to use regular expression parsing on all user-generated input. There are two approaches to this, Blacklisting or Whitelisting.

Blacklisting works by disallowing characters, words and phrases commonly used for injecting malicious code. This is the least effective of the two methods because clever hackers have methods of circumventing just about any usable blacklist. Whitelisting uses regular expression parsing to allow ONLY characters, words and phrases that are included in the whitelist.

In either method, multiple whitelists and blacklists should exist for different types of user input. For example, a selection made from a list box or radio button should be whitelisted against a set of legitimate values in the list.

Free text is more difficult to sanitize. However it is much more difficult to construct injection code in sql server if the characters ‘’’ ‘+’ ‘—‘ ‘&’ and similar symbols are not allowed by the whitelist.

In both methods, you need to develop multiple lists for different types of input. For example, when an input requires integer data, the white list you use might be quite small, e.g. “0123456789”.

Free text input is the most difficult. Normally you will disallow certain control characters like single quotes, semi-colons and SQL keywords like SELECT, DELETE, UPDATE, etc. Lists for free text input lists are often quite long.

However, we come up against the same problem with legacy code. It can be so much work that it is impractical to construct multiple lists and parse all inputs in a large, complex application.

It is difficult to construct lists that do thorough job.That is why it is critically important that you lock down the privileges of the application login if not to prevent sql injection, at least to control damage.

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call at 503.914.6442 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

SQL Injection, is still around.

Elsewhere on this site:

Call SQL Consulting
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator