How can I avoid SQL injection attacks?

Question

Yesterday I was speaking with a developer, and he mentioned something about restricting the insertions on database field, like, strings such as -- (minus minus).

At the same type, what I know is that is a good approach to escape HTML chars like <, > etc. Not --. Is this true? Do I have to worry about --, ++? Is it more like a myth or old stuff?

---

Update

Thanks a lot for all the answers, it's easy to understand like that since I'm kind of new to all of this. Well, to be more specific in this case our discussion was about and C# ASP.NET MVC website we're developing, so there's a complex open an account form in there with important information, so I'm not sure if MVC using Linq to interface with database already comes with this kind of protection or not. So if anyone could provides some hints about it, it would be great. Thanks again

Answer 1

He was talking about SQL Injection attacks, as is quite right in what he said.

The problem is not with such data existing in the database, but in passing input data directly to the database without sanitizing it.

Without cleaning it up, if someone passes in a string ending with a ; they can then follow it with anything they want (select * from sys.objects, for example) or something more malicious, like dropping some tables.

It is difficult to guard against fully, but if you use a good DB library from your code and follow known practices, such as using paremeterized queries you limit the possible damage.

Store as many -- in your database as you want, but do not pass that through to your database without going through a cleanup process (this is where a good DB library is vital - it should cleanup quotes and other potentially harmful input).

Answer 2

There's nothing "dangerous" about inserting a string containing -- in a database.

It is dangerous to insert anything in a database table that comes directly from user input without processing it, otherwise you leave yourself open to SQL injection attacks. Example: A coder lets the user type in their name in a field, and the user types:

Joe '); drop table users; commit transaction; --

and then the coder puts that in their MySQL database like so:

conn.execute("insert into users (username) values ('" + userInput + "')");

Boom The user has deleted the users table (assuming the database login had rights to do that, which it shouldn't -- but that's a different topic), because the coder didn't ensure that the string from the user was escaped correctly, and so it got sent directly to the DB engine and the attacker has a good laugh. :-)

Use whatever tools your environment provides to ensure that strings are escaped correctly. For instance, JDBC uses the PreparedStatement class for this. Most environments will have something similar.

Answer 3

Use parameterized queries. These queries represent the variables as a placeholder in the SQL, such as select * from person where name = ?. After creating the SQL query, you set the parameter values in the query. Parameterized queries ensure that whatever was substituted for the placeholder will not be considered as part of the SQL statement.

See Jeff Atwood's article for a good overview of parameterized queries.

Answer 4

SQL injection is a high security risk for most websites that allow users to squirt parameters into a statement that gets executed on a database.

A simple example would be:

Input field "Name: ___

"SELECT * FROM tblCustomer WHERE Name = '" + nameInputField + "'"

So if I type in "Bob" we have

"SELECT * FROM tblCustomer WHERE Name = 'Bob'"

But if I type in "'; DROP TABLE tblCustomer", we end up with the rather more sinister

"SELECT * FROM tblCustomer WHERE Name = ''; DROP TABLE tblCustomer"

There are lots of ways to avoid these problems, and many are built into whatever language you are using - so rather than think of all the dodgy possibilities ";", "--", "/*" etc, try and use something that already exists.

Shout out what language you're using and I'm sure we can tell you how to avoid these attacks.

Answer 5

The proper way to avoid SQL Injection attacks is not so simply disallow certain problamatic characters, but rather to use paramaterized SQL. You can find some good articles on the subject of paramaterized SQL here:

http://www.codinghorror.com/blog/archives/000275.html

http://www.codeproject.com/KB/database/ParameterizingAdHocSQL.aspx

Now that you mentioned that you are working with ASP.net I can give you some links that deal specifically with SQL Injection in ASP.

http://en.csharp-online.net/ASP.NET_Security_Hacks%E2%80%94Avoiding_SQL_Injection http://www.codeproject.com/KB/aspnet/SQL_Injection_.aspx?msg=3209511

Here is a more general article on making your ASP more secure: http://www.codeproject.com/KB/web-security/Securing_ASP_NET_Apps.aspx

And, of course the MSDN article on SQL injection: http://msdn.microsoft.com/en-us/library/ms998271.aspx

Answer 6

It is not dangerous as long as you correctly escape the data when doing INSERT/UPDATE/...

And escaping HTML characters is NOT a good approach. Imagine you wrote a function that escapes such characters and you have stored some escaped text in the database. Then you notice that your function did not escape '<', so you change the function... now what happens to the records that are already in the database? - Their '<' characters will stay unescaped. Thus, NEVER escape text before storing it in the database (escape the SQL query, of course). Escaping should happen when the HTML/XML/... page is produced out of the text, that is, after querying the original text from the database!