SQL Injection Hack for ASP/MS-SQL when submitting a URL
I am working on a site that was just given to me and is pretty much done. There is already a function to replace sql injection characters/words into a safe format (such that something like 'select' is replaced with 'selects').
However, there is one place there is a problem, and it is when the user is inserting a URL. So, for instance, the user enters a url with a ; (semi-colon) in it. Obviously, I don't want that added, however, if I replace it with any other character, the URL will no longer be good. I was thinking of using URl encoding so that 'http://test.com?id=123;32' equals 'http://test.com?id=123%3B32', however, when trying to reach the URL with that address it does not work with sites like youtube when I replace the "&" with the "%26".
I do not mind removing keywords such as delete, update, select, insert - however, I will need access to allow users to enter in data such as ; or -- which a lot of URL's these days have (such as yahoo news and youtube).
Do any of you experts out there now the best solution to make sure the URL is posted correctly without me exposing myself to a potential hack? This is dynamic SQL and I will not have been told by the owner that I do not have the ability to create a stored procedure.
Thanks
However, there is one place there is a problem, and it is when the user is inserting a URL. So, for instance, the user enters a url with a ; (semi-colon) in it. Obviously, I don't want that added, however, if I replace it with any other character, the URL will no longer be good. I was thinking of using URl encoding so that 'http://test.com?id=123;32' equals 'http://test.com?id=123%3B32', however, when trying to reach the URL with that address it does not work with sites like youtube when I replace the "&" with the "%26".
I do not mind removing keywords such as delete, update, select, insert - however, I will need access to allow users to enter in data such as ; or -- which a lot of URL's these days have (such as yahoo news and youtube).
Do any of you experts out there now the best solution to make sure the URL is posted correctly without me exposing myself to a potential hack? This is dynamic SQL and I will not have been told by the owner that I do not have the ability to create a stored procedure.
Thanks
One more example showing Classic ASP and SQL Server
http://www.userfriendlythinking.com/Blog/BlogDetail.asp?p1=7013&p2=119&p7=3001
http://www.userfriendlythinking.com/Blog/BlogDetail.asp?p1=7013&p2=119&p7=3001
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Data should be encoded for the target context. The target in this case is MS-SQL so the data should be encoded for MS-SQL (not URL; not HTML) so that it can't break into the context.
lammy82 is spot on with using parametrized queries which should handle encoding for you, but take a look at this excellent resource:
http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
lammy82 is spot on with using parametrized queries which should handle encoding for you, but take a look at this excellent resource:
http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
I think SimpsonThePhil's answer is the wrong one and, for your sake and that of others who will undoubtedly view this question, would like to make amends for not making myself clear enough in the first place by expanding on my answer.
Encoding SQL data for HTML will NOT prevent injection into the MS SQL query.
Note that:
"If this is a web application, them HTML encode it before you store it, then HTML decode it when you display it."
is at best misleading (it is actually plain wrong). Aside from the fact that encoding for HTML will not prevent injection, consider that, in a web application you will likely wish to output the data into an HTML context (such as displaying it in a web page) and it should be ENCODED for HTML before displaying it - not decoded.
To prevent SQL injection, you should:
Encoding SQL data for HTML will NOT prevent injection into the MS SQL query.
Note that:
"If this is a web application, them HTML encode it before you store it, then HTML decode it when you display it."
is at best misleading (it is actually plain wrong). Aside from the fact that encoding for HTML will not prevent injection, consider that, in a web application you will likely wish to output the data into an HTML context (such as displaying it in a web page) and it should be ENCODED for HTML before displaying it - not decoded.
To prevent SQL injection, you should:
- Canonicalise the input. That means reduce the input to it's simplest form. e.g. the input may be URL encoded and needs decoding until it cannot be decoded any further and you should throw an exception if the input contains detect mixed or multiple encoding since this is likely an attack).
- Validate the data. Check that it is of acceptable type and length and that it contains only those acceptable characters defined in a white-list.
- If the data validates successfully, it should preferably be fed to a parameterized query (aka prepared statements), but if this is not possible then the data should be escaped (encoded) for the specific database in use. That means, in your case, escaping characters that may allow data to break out of context in a MS-SQL database.
- http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
- http://blogs.msdn.com/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx
Hi,
Dont think you quite understood the answer I gave, what you are attempting to is prevent a dynamic sql statement from being escaped and unitended code injected into the statement so the statement does not perform the intended task.
The answer I was attempting to give was to encode the data before it is included in the dynamic statement, ie if somebody attempted to inject code into the statement the variable in question would be encoded before appending into the dynamic statement.
The type of encoding is irrelevant i.e you could use base64 encoding to perform the same job. The end result is the server in question will not act on the data you are passing as if it was a legitimate statement. Encoding just ensures this will not happen (unless someone figures out your encoding and places the correct sequence of characters in the input to decode to a valid sql statement in the encoding output - unlikely).
The drawback to this solution is that queries based on the encoded data are difficult to run server side. You could also employ third party filters on your IIS server to attempt to strip out this at the IIS level. Many medium and top end firewall will also filter this type of attack but the success varies.
Ensure that your SQL server is locked down and that your sql account has the lowest priviledges, we go futher and physically remove the dlls that provide any extended stored procedures that are not needed, simply dropping these from the server is not enough if a user gains admin level access.
The most common SQL injection attempts on an ms platform is to use xp_cmdshell to create a user account and then esculate privleges to admin level. Systems with public facing terminal or RDP servers are then extremely compromised.
Apologies if my first attempt at answering was a little vague, I was starting my second bottle of wine.
Phil
Dont think you quite understood the answer I gave, what you are attempting to is prevent a dynamic sql statement from being escaped and unitended code injected into the statement so the statement does not perform the intended task.
The answer I was attempting to give was to encode the data before it is included in the dynamic statement, ie if somebody attempted to inject code into the statement the variable in question would be encoded before appending into the dynamic statement.
The type of encoding is irrelevant i.e you could use base64 encoding to perform the same job. The end result is the server in question will not act on the data you are passing as if it was a legitimate statement. Encoding just ensures this will not happen (unless someone figures out your encoding and places the correct sequence of characters in the input to decode to a valid sql statement in the encoding output - unlikely).
The drawback to this solution is that queries based on the encoded data are difficult to run server side. You could also employ third party filters on your IIS server to attempt to strip out this at the IIS level. Many medium and top end firewall will also filter this type of attack but the success varies.
Ensure that your SQL server is locked down and that your sql account has the lowest priviledges, we go futher and physically remove the dlls that provide any extended stored procedures that are not needed, simply dropping these from the server is not enough if a user gains admin level access.
The most common SQL injection attempts on an ms platform is to use xp_cmdshell to create a user account and then esculate privleges to admin level. Systems with public facing terminal or RDP servers are then extremely compromised.
Apologies if my first attempt at answering was a little vague, I was starting my second bottle of wine.
Phil
Here are some examples:
http://davidhayden.com/blog/dave/archive/2005/10/24/2528.aspx
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=6999&lngWId=4