Nervous about getting into database-driven web development due to SQL injection

Question

Possible Duplicates:
What is SQL injection?
In PHP when submitting strings to the DB should I take care of illegal characters using htmlspecialchars() or use regex?

I really need someone to clearly explain how to handle hackers and if it's really as complicated as it sounds.

Answer 1

The basic thing to do about sql injection is to use parameters in your queries.

So, this is BAD, and could be used for injecting hackers's sql:

mySqlCommand.Text = "select * from mytable where FirstName = " + tbox.Text;

Do it this way:

mySqlCommand.Parameters.Add("@FirstName", tbox.Text)
mySqlCommand.Text = "select * from mytable where FirstName = @FirstName";

Note: I used "pseudo code". I Don't know php or mysql, but the same principle should apply.

Answer 2

I believe that this question can be answered if I point you to this thread:

• Parameters to avoid SQL Injections

I would not get worried about injections. There are lots of people facing that, and you can find a lot online. Remember to sanitize ALL the data that will finally end up being queried.

EDIT:

Reading about it, I found this pretty interesting about parameters techniques when you are doing Dynamic SQL. If that's the case I would read this:

• Parameters techniques for Dynamic SQL

Answer 3

http://php.net/manual/en/function.mysql-real-escape-string.php

Basically escape any input you receive from the user. Providing it doesn't break anything for you it is often worth sanitizing the entire $_POST (or $_GET) array.

There are well written functions at the link above that recursively "sanitize" the input making it safe to use in your code.

Your worries are warranted, but SQL injection is not very difficult to protect against. Usually SQL vulnerabilities exist due to carelessness in my experience.

---

Often SQL injection in it's basic form is just adding a single qoute or double qoute to the input so as to close your SQL field:

$user = "john"
$query = "SELECT * FROM table WHERE username='$user';"

Sets $query to "SELECT * FROM table WHERE username='john';"

Notice, however, that if we set $user = "' OR 1==1 OR username=''" (Note the single qoutes) We get:

"SELECT * FROM tab;e WHERE username='' OR 1==1 OR username='';"

This query would always return true, thus a successful attack occurs. If we escape the input the single qoutes are escaped such that they aren't interpeted like above. (Think of it like adding a backslash to escape the single qoutes that come in via the $user variable.) This is a simple example and more complicated SQL statements can do alot more damage, but the method of attack is the same.