Correct way to sanitize input in MySQL using PDO

Question

so I had a friend of mine try to run a SQLinjection on my site and he managed to get into it using the code underneath. How can I prevent this? I have read something about sanitizing the variables but how do I do this?

';INSERT INTO login (username, password) VALUES ('Gjertsmells', 'password');SELECT 'password' FROM Login WHERE 'x'='x

$db = new PDO('mysql:host=XXXXXXXX;dbname=XXXXXXX', 'XXXXXXXXXX', 'XXXXXXXXX');

        // query MySQL to verify login
        $query = $db->prepare("SELECT password FROM login WHERE username='$username'");
        $query->execute();

        $column = $query->fetchColumn();
        if($column === $password)

This may be helpful, http://stackoverflow.com/questions/3783627/sanitizing-variables-with-pdo — Nikhil Mohan, Jun 14 '13 at 07:36

score 8 · Accepted Answer · answered Jun 14 '13 at 07:34

8

The idea of prepared statements is that you don't concatenate variables, instead you bind the parameters. The difference is the variable never gets inserted into the SQL, rather the MySQL engine handles the variable separately which leaves no possibility of SQL Injection. This also has the added bonus that no escaping or pre-processing of the variable is required.

$query = $db->prepare("SELECT password FROM login WHERE username = :username");
$query->execute(array(':username' => $username));

answered Jun 14 '13 at 07:34

MrCode

63,975
10
90
112

Does this prevent SQL injection such as the code used on me earlier? and am I still vunreable to other common attacks? – Jun 14 '13 at 07:38
2

It only protects the query that you apply it to, you need to do this for all your queries that take parameters. It only prevents SQL Injection, there is an array of other common vulnerabilities (XSS, Open Redirect etc etc), which each require a separate defence mechanism. – MrCode Jun 14 '13 at 07:40
Is it required to use for the following aswell? `$column = $query->fetchColumn();` – Jun 14 '13 at 07:40
$query->fetch() or $query->fetchAll() – MisterBla Jun 14 '13 at 07:41
Well it is required on any query that takes parameters. `fetchColumn()` is irrelevant because that is just an operation on the subsequent result set of a query. – MrCode Jun 14 '13 at 07:42
What is the difference from what I have set @RichardA? – Jun 14 '13 at 07:43
@FriedBitz fetchColumn() returns a single column from the next row of a result set or FALSE if there are no more rows, fetch() will grab the next row instead of the column and fetchAll() will grab everything. – MisterBla Jun 14 '13 at 07:44
@FriedBitz it makes no difference what you do with the result, it's the execution of the query where SQL Injection occurs. – MrCode Jun 14 '13 at 07:45
Sooooo, how does this relate to checking if password is correct? @RichardA. Thank you MrCode, waiting for answer accept again, some cooldown on it. – Jun 14 '13 at 07:49
@FriedBitz Probably doesn't, there are multiple ways of fetching them. Didn't understand the statement. Sorry about that. – MisterBla Jun 14 '13 at 07:53
No worries @RichardA, just curious how it changed my alleready exisiting statement :) – Jun 14 '13 at 07:55

score 6 · Answer 2 · answered Jun 14 '13 at 07:34

Prepare your statement like this:

$query = $db->prepare("SELECT `password` FROM `login` WHERE `username` = :username");
$query->execute(array(":username" => $username));

Or bind the parameters using the same prepared statement like this:

$query->bindParam(":username", $username, PDO::PARAM_STR);

$query->execute();

This way you shouldn't have to sanitize your query.

score 3 · Answer 3 · answered Jun 14 '13 at 07:35

3

Don't sanitize input. Just make sure that you really write to the database what ever data is provided (i.e. protect against SQL injection) and then escape your output.

To protect against SQL injection, use bound parameters. To escape your output, use htmlspecialchars on web pages and any other encoding appropriate given the medium you are outputting to.

Just remember that you have to do both of the above. If you only protect against SQL injection attacks, you'll still leave your site wide open to XSS attacks.

answered Jun 14 '13 at 07:35

mzedeler

4,177
4
28
41

How would you define XSS attacks? Just so I can identify how they work. – Jun 14 '13 at 07:41
There is a very large article here: https://en.wikipedia.org/wiki/Cross-site_scripting and an example here: http://www.thegeekstuff.com/2012/02/xss-attack-examples/ – mzedeler Jun 14 '13 at 07:58

Correct way to sanitize input in MySQL using PDO

3 Answers3

Linked