• Welcome to TechPowerUp Forums, Guest! Please check out our forum guidelines for info related to our community.

SQL Injection FAQ

Oliver_FF

New Member
Joined
Oct 15, 2006
Messages
544 (0.11/day)
Processor Intel q9400 @ stock
Motherboard Lanparty P45-T2RS
Cooling Zalman CNPS-9500
Memory 8GB OCZ PC2-6400
Video Card(s) BFG Nvidia GTX285 OC
Storage 1TB, 500GB, 500GB
Display(s) 20" Samsung T200HD
Case Antec Mini P180
Audio Device(s) Sound Blaster X-Fi Elite Pro
Power Supply 700w Hiper
Software Ubuntu x64 virtualising Vista
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:
Code:
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:
Code:
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
Code:
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 \ !
Code:
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

Senior Monkey Moderator
Joined
Feb 6, 2007
Messages
13,817 (2.92/day)
Location
Cheeseland (Wisconsin, USA)
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.
Code:
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.
 
Last edited:
Joined
Aug 10, 2007
Messages
4,228 (0.93/day)
Location
Geneva, FL, USA
Processor Intel i5-6600
Motherboard ASRock H170M-ITX
Cooling Cooler Master Geminii S524
Memory G.Skill DDR4-2133 16GB (8GB x 2)
Video Card(s) Gigabyte R9-380X 4GB
Storage Samsung 950 EVO 250GB (mSATA)
Display(s) LG 29UM69G-B 2560x1080 IPS
Case Lian Li PC-Q25
Audio Device(s) Realtek ALC892
Power Supply Seasonic SS-460FL2
Mouse Logitech G700s
Keyboard Logitech G110
Software Windows 10 Pro
If you're running on PHP5, PDO has prepared statements.

PHP:
$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!
 
Top