Understanding SQL Security

For many corporations, their relational databases represent the accumulation of months and often years of hard-earned data, and these databases are usually guarded by a veritable army of highly paid database administrators (DBAs). As a SQL developer, you need to understand some SQL security issues so you don't create security bugs that can jeopardize the integrity of these databases.

As an example, what's wrong with an application that uses the following SQL connection string, taken almost directly from the .NET SqlConnection documentation?

 ConnectString = "user id=sa;password=Jx2pt; initial catalog=pubs;data source=mySQLServer" 

The security bug here is the use of system administrator (sa) as the SQL Server login for the application. The system administrator in this context can do almost anything that he or she wants to the database. If your end user or another developer manages to hijack your application in some way, your database could be in serious trouble. Tables could be dropped, data could be deleted or corrupted, or any other of a host of painful scenarios would be possible.

You might believe that it's not possible to hijack your application, but why take the risk? It's far more secure to either use Windows integrated security for your application's SQL Server login or set up a new user that only has the power to execute specific stored procedures. In this way, you can limit the amount of damage that can be done by a rogue developer or end user who abuses your code in order to find a way into your SQL Server.

It's also easier to hijack an application that uses SQL Server than you might think. Imagine that your program allows its end users to read a database table called AppUser, which contains a list of authorized users of your application. You give the user a text box where he or she enters an identifier for the user whose details he or she wishes to see. Then you take the ID entered by your end user, put it into a variable called UserId , and add it to the end of a custom query looking something like this:

 SqlQuery = "SELECT * FROM AppUser WHERE Id = " & UserId.ToString 

So when the end user enters something like 2 as the user ID, this query returns all the details for the application user who has the ID of 2. But instead of just entering a user ID, a malicious end user could try entering the following text:

 2 INSERT AppUser VALUES('Mark', 'Pearce', 'SuperUser') 

When you add this user-entered string to the end of your SQL query string and then execute the query, you'll suddenly find a new authorized application user in the AppUser table with superuser rights. This technique is called SQL injection , and it's a serious danger for any application that performs SQL queries with the help of text entered by the user. To avoid this type of security bug, you should always treat any user input as dangerous and validate the information before using it in a SQL query. In this case, for instance, you could validate that the text entered by your end user was numeric, which would immediately throw out any dangerous SQL injection.