SQL Injection Prevention Techniques
-
Hello gurus and the newbie’s. I have a question on SQL Injection. So far I have done my own research on this subject for a while now and techniques for preventing SQL Injection ranges from Different permissions for connection from the APP layer to User Input validation. And of course I am making sure that all these prevention techniques are incorporated into our application but there is one prevention technique that I can't avoid due to the type of application we are developing and that is DYNAMIC SQL. As a result, I am looking for the best solution/technique to prevent SQL Injection (in the Database Layer) knowing that Dynamic SQL will be used in the application. My first and best solution is to write my own sp_executesql (called my_sp_executesql) and instead of developers calling the system supplied sp_executesql, they will call my_sp_executesql. Within this stored proc, I will parse the dynamically generated T-SQL Code for certain reserve words/characters like, DROP, CREATE, SETUSER, ALTER, --, ;, etc. If any of these exist, it will return a user define message to the caller. My question is, is this the best solution for preventing SQL Injection in the database layer of the application? Or is there another way, knowing that Dynamic SQL will be used in the application?
-
Better still would be to write a function that takes a string and returns back a string. Replace any code that you're concerned about. For instance, single quote becomes two single quotes, semi-colons are checked, dashes and /* and */ are as well. Require all stored procs to use said function. Even in the case of dynamic SQL calls require the use of stored procedures. Granted, the last is more of a procedural way of controlling, but then too would be rewriting sp_executesql.
Also, ensure the user for the application doesn't have the ability to DROP, CREATE, ALTER, etc. Give it only the rights it needs and nothing more.
K. Brian Kelley
@kbriankelley -
You should read an article writen by: Erland Sommarskog:
http://www.sommarskog.se/dynamic_sql.html
I think you will find interesting informations on using dynamic sql and sp_executesql.
Hope it will help.
Carl
-
That article is a good start for SQL injection, but a bit on the brief side. That's because the author (a respected MVP) is concentrating more on the overall issue of using dynamic SQL, not strictly from a SQL injection perspctive. It's still a great article.
You'll find two articles from NGSSoftware that are really good and numerous other ones scattered on the various security sites. Look for them on OWASP and SecurityFocus, among others.
Brian Knight also has an excellent webinar he did through Microsoft that shows the process of working through error-based SQL injection. You'll have to search for it on the net, I don't have a link handy.
K. Brian Kelley
@kbriankelley -
Thanks Brian,
I will read those articles.
But, If ever you find any URL, can you post it?
Bye
Carl
-
Sorry, was in the middle of walking an admin at a remote site on troubleshooting his server's failure to register in DNS as I was typing the reply. He didn't put DNS servers in his TCP/IP settings. That would also explain why he couldn't get out to Google. Argh.
Okay, for the links.
Advanced SQL Injection in SQL Server Applications (NGSsoftware)
(more) Advanced SQL Injection (NGSSoftware)
Writing Secure ASP Scripts (NGSSoftware)
SQL Injection: Are Your Web Applications Vulnerable? (SPIDynamics)
Blind SQL Injection: Are Your Web Applications Vulnerable? (SPIDynamics)
Technet Webcast: SQL Server 2000 Security Best Practices (Brian Knight)
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply