SQL Server Blog

My Blog

Monday, January 13 2014

Keywords: SQL Special Characters, Oracle Escape Characters, Avoid SQL Injection, SQL Security

Avoiding SQL Injections & Handling Special Characters in SQL Server & Oracle

This is a condensed check-list of some of the first steps you could take in protecting your .NET application and more importantly your data, from SQL injections and special characters in queries. I will not delve into a complete and thorough .NET security lesson here. Let's make sure that the basic security best practices are being addressed first.


1) Parameterization!...Use of Parameterized context of .NET this basically translates to one thing: use the Command object, and have a parameters collection in it as a placeholder of opposed to executing a long string containing SQL.


2) Use Stored Procedures!...They add another layer of protection and abstraction in to the design of your application. Now you could change the underlying table structure with little or no signature changes in the stored procedure. You are not exposing your tables directly to the application and the end users; and what's best is that you could call them using parameters. And they're fast too!


3) Avoid dynamic SQL!...Avoid using Execute or sp_executeSql or any other commands that will help you in constructing and executing a SQL string, unless you have 100% control over parsing the parameters and fully escaping the especial characters. This IS of course the main gateway to SQL injection and most database security problems in the last two decades. In fact most hacks started in the login page of websites, by doing SQL Injection where you look up the users table with the entered username and's so easy actually that it is hard to believe.


4) No direct access to tables!...I recommend that you do not provide any direct access to your underlying tables. You might tell me that your application does not do that BUT if the user has the permission to do so, that's bad enough...they have permission to access the tables directly and they might do so in the future, putting your database's security in jeopardy. The best option would be to limit the permissions of the user, disallowing them direct table access. This works out best, especially since it would not allow a new middle-tier or U.I. developer in the group to write code that would suddenly access the tables directly, doing so without database architect's knowledge or approval...These sort of things happens often actually and it is normal: DBA's are either non-existent in some companies, or otherwise there is only one or at best a few of them in a company...and all so developers do not like to wait for them or depend on them too much, they like to develop the database codes/scripts fast fast...and optimize it later! Of course the optimization part is soon forgotten and never brought up again...until a QA staff or a client complains.


5) Apply the 'Least Privilege' principles!...Give SQL Server services the least amount of permissions they need to run...Give your application user the least amount of permissions it would need to operate at 100%...never provide sysadmin privileges to your application...even dbowner is often too much. You could have multiple users in the application, each with different level of security...You could also utilize proxy accounts and Credentials.


6) Encrypt Sensitive Data!...So that even if they do waste their time to hack into your database...they get absolutely nothing of value.


7) Beware of error messages!...Be careful not giving away too much company secrets or database schema structure in your error messages. You might want to log detailed error messages somewhere in the server or the database for future reference, but only show a summarized/edited version to your clients...table names, column names and user name etc should not be displayed to the end user...otherwise you might be assisting them in their injection/hacking plot without knowing.


8) Escape special characters in LIKE type searches...If your data access layer or parameterization do not already do so, then you would need to handle certain special characters. Test your application fully (using special characters) to see how it is handling it now and only escape characters if deemed necessary (escaping could slow down the application).
  A) Oracle
   Underscore (_) by using \_
   Ampersand (&) by using \&
   Single Quote (') by using two quotes ''
   Percentage (%) by using something like: select * from tblTest where somefield like 'Hello\&World' ESCAPE '\'
  B) SQL Server
   Single Quote (') by using ''
   Open brackets ([) by using [[]
   Underscore (_) by using [_]
   Percentage (%) by using [%]



Written by Montreal DBA Team