What are Some need to Know SQL Injection Techniques that Hackers use

Question

I am tightening down my web application and I am on SQL right now. I already have sql parameters, doubling apostrophe, stripping javascript and encoding html. Is there other things I need to worry about besides the things above?

Answer 1

If you use Parameterized Queries, then you shouldn't need to do any of the stuff you describe in your post. This article seems to be a pretty good discussion about the subject.

To restate for emphasis, the silver-bullet for protecting against sql injection attacks is to only pass user input into sql via parameterized queries.

Answer 2

Make sure all inputs are contained in quotes. If you have something like ... accepted=1 or age=30 ... you could possibly have a security hole there: ... accepted=1; DELETE....

EDIT: Yes the attacker could use 1"; DELETE, but that doesn't work with escaped input.

Answer 3

Parameterized queries are only needed if you are dynamically generating queries using data from the user.

There are benefits for using these queries on something like oracle, which is also useful.

Something else that you can do is to ensure that the user that connects to the database has the fewest privileges needed.

You can look here for some suggestions about protecting against sql injection: http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

Depending on your paranoia you can just use stored procedures to do that access, but that is more difficult if you need to make dynamic queries.

For select you can use views, that way it isn't possible, unless you have triggers on the view, to use that query to change any data in the database.

Another page that can be used is here: http://www.wwwcoder.com/Directory/tabid/68/type/art/site/2966/parentid/258/Default.aspx

But, there is no point in putting more security than is reasonable, so you should weigh what you are protecting and how far to go.

For example, is it worth it to encrypt some of your columns? That leads to more issues as to how to protect keys, but, if you are worried about someone hacking into your machine and getting the database files that may be something to consider.

Answer 4

I'd recommend to download the automated Microsoft Source Code Analyzer for SQL Injection and let it analyze your code first, you may reap some cheap low hanging fruits.

Answer 5

A cyberpunk can get a perl shell from the uri query via concatenation. 3 solutions are use stored procedures, preparedstatements or gql instead.

Answer 6

Technically Javascript and HTML have nothing to do with SQL injection attacks, but they are injection attacks.

I don't agree with stripping, at least not without informing the user that you have done so. For one, the actual input might actually be valid, removing certain characters might actually change the semantics of the input, and you normally can't be sure at storage time about every medium that the value might be rendered under. What about URL, LDAP, XML or File Path injection attacks.

You will find a lot of security "priests" (parrots I say) out there that argue that SQL injection is best protected by stripping the characters that can cause damage from the data entered. That is fine until fine as long as your not going to accept O'Brian as valid input for exmaple. It would have been better if they had preached about correct encoding, because all that effort might have been used to address a whole bunch of injection attacks.

Actually I once had to deal with code written by a MS security expert who wrote something like:

userInput = userInput.Replace( "--", "").Replace( "'", "");

In fairness to the fellow he is a very clever chap and I'm sure he wouldn't write this today, but can you see how "MyName -'- SELECT * FROM User" would actually get around this protection?

It's fine to have a range of characters you will accept and others that you won't as long as you don't block valid values. < is also a valid mathematical operator, or maybe the user needs to enter example HTML code. The context of the value determines what is valid and what isn't.

Then if you get a value including a character that is not allowed, display an error back to the user telling them about the invalid values and ask them to fix it before resending. This can save a lot of grief later, and this is as much about data validation and ensuring your program functions as it is about security. And yes you need to check this both at the client side to keep the traffic down and at the server side to prevent attacks.

And then whenever your render the value encode it appropriately. Parameterized queries are fine for solving SQL injection, but they don't work for rendering HTML, URL and javascript. Correct SQL encoding of values (you mentioned the double single quotes for example) is just as safe and in some cases preferable (assuming you have reasonable DBAs who don't insist that everything has to be Stored Procedures and allow correctly encoded dynamic SQL).

One other point, the System.Web HTML and URL encode functions have some flaws. There is a Microsoft team that produced the Anti Cross Site Scripting library which addresses these issues, and provides more specific encoding options. I think its called Anti-XSS or something like that. Definitely worth having a look at. I wonder if it got incorporated into .Net 4?

Answer 7

Parameterized queries go a long ways towards keeping the baddies out, but make sure there aren't strange, injectable dynamic sql concoctions behind them opening new vulnerabilities.

Other, lunatic fringe idea: don't run popular open source packages, especially those written in languages starting with P.