# SQL Injection FAQ



## Oliver_FF (Sep 23, 2008)

WARNING - Performing SQL injection when you do not have permission to do so is ILLEGAL!!!

*Intro*
More and more services are appearing on the Internet, and most of these are connected up to databases containing vast heaps of data. Blogs, Wikis, Forums, Shops etc etc are all tied up to databases which feed them data. Now databases are accessed via some special software called database management systems, which take queries and process them in the most effective manner to return some data. There is a horrible crossover point in code for these services whereby user input on a web form or something needs to be put into a query to a database, say you want to log in on a forum, you need to get details about the person who is trying to log in, so you need to pass their name to the database to search for it. This crossover point, where you have to inject data from the user into some SQL, is where problems arise for security. The problem mainly exists because of crossing the gap between two different languages.

*Example *
Suppose there is a web form for someone to log in to a forum. There's some PHP somewhere that might look something like this:

```
user = someform.userInput???
pass = someform.password???
results = executeQuery("SELECT username, password FROM userTable WHERE (username == '" + user + "') AND (password == '" + pass + "');");
if (results.count() == 1) return SUCCESS;
```

If someone enters *fred* and *letmein* the query would look like:

```
SELECT username, password FROM userTable WHERE (username == 'fred') AND (password == 'letmein');
```
This will get freds entry in the database provided the username and password match up.

Consider now if someone entered this *fred* and *' or '1'=='1*

```
SELECT username, password FROM userTable WHERE (username == 'fred') AND (password == '' or '1'=='1');
```
Examine that a bit closer - every row where the user is 'fred' and EITHER the password is blank, or TRUE... The select will return freds entry from the database! Suddenly this person is logged in as fred!

This is a serious problem for a lot of people in todays world, despite it being a very simple thing to prevent.

*Preventing SQL Injection*
It's easy - just delimit all special characters with a \ !

```
SELECT username, password FROM userTable WHERE (username == 'fred') AND (password == '\' or \'1\'==\'1');
```
This now won't get anything back. Problem solved.

Many languages have built in functions to prevent SQL injection. In Java, you have to use the *PreparedStatement* class which automatically delimits special characters preventing you from attack by using *Placeholders*. In PHP there are several functions to do it, like *addslashes()* and *mysql_real_escape_string()*

There is a LOT more information about this on Wikipedia!! Lots!!

So remember, if you're working with SQL and a web language DELIMIT SPECIAL CHARACTERS IN THE INPUT!!


----------



## Kreij (Sep 23, 2008)

Good article Oliver_FF. 
I also mentioned the importance of sanitizing user inputs into databases in one of my coding articles. 
It's something that every coder who writes for database applications should keep in mind.

In your case you show a simple method of accessing the database through injection, but there are many people who would not think twice about injecting destructive sql statements as inputs that could obliterate your entire database.

Two general rules to follow ... Satitize your inputs before sending them for executions and MAKE BACKUPS OF THE DATABASE !! (Because there is the possibility you missed something  )

In my Hash Encrytion article I mentioned using a short method to weed out any unwanted characters from user input (not just spaces). Looked like this.

```
public string sanitizeInput(string thisInput)
{
    Regex regX = new Regex(@"([<>""'%;()&])");
    return regX.Replace(thisInput, "");
}
```

Obviously you could change the regular expression for whatever you wanted to invalidate/validate.


----------



## Disparia (Sep 24, 2008)

If you're running on PHP5, PDO has prepared statements.


```
$dbc = new PDO('mysql:host=localhost;dbname=nameOfDB', 'user', 'pass');
$sql = 'INSERT INTO some_table (some, info) VALUES (?, ?)';
$stm = $dbc->prepare($sql);
$stm->bindValue(1, 'SQL INJECTION CODE', PDO::PARAM_STR);
$stm->bindValue(2, 'OH NOES!', PDO::PARAM_STR);
$stm->execute();
```

There is also the bindParam method, useful for looped inserts.

A few weeks ago while watching the 'user tracker' on one of my sites I noticed that someone tried to SQL inject through a form. If successful, it would have appended some javascript to every string type field in the table. Searching the web for that code showed a bunch of sites that been injected - including supermicro.com!


----------

