SQL Injection

[Colourtheory]

So I've been warned a few times about this, and I was wondering, how do I stop/prevent them? What methods are dangerous?

[Don E]

Check out this link for some information on SQL Injection: http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

[davej]

The risk is accepting any input from the outside and using it to create a SQL query. Even cookies and hidden fields can be easily edited by a hacker. The two primary defenses are to sanitize all external inputs with a regex (and a length limiter for XSS) and to use parameterized queries.

[thescientist]

using PDO if you are writing you scripts in PHP and is supported by your version of PHP is a good practice as well as servier side sanitization/validation of input. basically, if you are expecting a username, there shouldn't be anything but values from a-Z, if you are expecting a zip code, it should only be numbers, and five digits long, etc. If you can validate client side with JS, that is a convenience for the user, but you should also perform the same validation server side. never trust user input.

Edited December 25, 2012 by thescientist

[jardrake]

This highly depends on what server side language you are using. Props to thescientist for recommending PDO because it is one of the awesome classes in PHP. Essentially, an sql injection occurs when a user injects code that looks like sql code and reads as sql code when put in the query. //partial PHP codeWARNING: this code is an example of bad code"SELECT username, passwordFROM usersWHERE username = $_POST['username']AND password = $_POST['password']" Suppose you have that query, and I send the post variables of $_POST['username'] = " 'aaa' OR 1=1 -- "Now you have, "SELECT username, password FROM users WHERE username = 'aaa' OR 1=1 -- The AND statement is now left out because it was commented, and now I get every username and password because 1 is always equal to 1. If for some odd reason you printed out those results to the screen like to show their username, I would have a list of all the usernames in your 'users' table. PDO escapes this issue with the execute statement and using placeholders in the sql query.