How to prevent mysql injection in Php before it is submitted to the database

Question

Possible Duplicate:
Best way to stop SQL Injection in PHP

Recently, I got my database wiped through a hacker inserting a DROP table command through a signup form.

This annoyed me and got me thinking:

How can I prevent mysql injection in php, before the information is sent to the database, e.g is there a way to detect that the user is trying to inject bad code into the database that can wipe it, and if so, detect it and display an error?

Also, is there a way to detect the mysql injection after it has been added, so when I am displaying the query, if it is the delete injection code, don't display it.

Thanks again, I apologise for any waffle.

You use prepared/parameterized statements. Then injections simply aren't possible (because all user input goes through the bound parameters, and the database knows its not a SQL command) — derobert, Dec 08 '11 at 18:52
I'd search for a question before you post it. There are many SQL injection prevention questions already. Not scolding just letting you know. — k to the z, Dec 08 '11 at 18:54
What function you are using to run your queries at the moment? — Your Common Sense, Dec 08 '11 at 19:56
See this topic: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — redreinard, Feb 04 '14 at 04:01

score 8 · Accepted Answer · edited Apr 18 '14 at 21:04

Use either PDO or Mysqli with the binding syntax. This alone will prevent most injection attacks.

Example:

    $stmt = $db->prepare(
                'UPDATE users ' .
                'SET userEmail=:email, userSalt=:salt, userPass=:pass ' .
                'WHERE userId=:userId LIMIT 1' );
    $stmt->bindParam( ':email',  $this->_email,    \PDO::PARAM_STR );
    $stmt->bindParam( ':salt',   $this->_salt,     \PDO::PARAM_STR );
    $stmt->bindParam( ':pass',   $this->_password, \PDO::PARAM_STR );
    $stmt->bindParam( ':userId', $this->_id,       \PDO::PARAM_INT );
    $stmt->execute();

In the above example, trying to escape the :email binding to insert a DROP TABLE won't work.

You still need to be careful with user-provided data. For instance, if the user provides a $docId for a get document query, make sure they're authorized for the document being requested. (And not just guessing a $docId belonging to some other user).

+1 for resisting the urge to mention Bobby Tables :) – Kalessin Dec 08 '11 at 18:54 — Kalessin, Dec 08 '11 at 18:54
Agreed, and I'd recommend PDO, simpler + portable. – CodeVirtuoso Dec 08 '11 at 18:55 — CodeVirtuoso, Dec 08 '11 at 18:55

score 0 · Answer 2 · answered Dec 08 '11 at 18:54

0

If you do not want to use PDO or mysqli I would suggest using transactions and only commiting to the database when you are sure that everything is correct.

answered Dec 08 '11 at 18:54

Naftali

144,921
39
244
303

score 0 · Answer 3 · answered Dec 08 '11 at 18:54

Use PDO or mysqli's bind parameters functionality:

http://php.net/manual/en/mysqli-stmt.bind-param.php

http://www.php.net/manual/en/pdostatement.bindparam.php

The PDO syntax is easier to use, but either will work:

$pdo = new PDO($stuff);

$stmt = $pdo->prepare('SELECT * FROM foo WHERE bar = :baz');
$stmt->bindParam(':baz', $baz);
$stmt->execute();

score 0 · Answer 4 · answered Dec 08 '11 at 18:54

0

<?php

function hashPassword($str)
{
        return hash("sha512", $str . "salt");
        //Change so it fits your database configuration.
}

$username = mysql_real_escape_string($_POST['username']);
$password = hashPassword($_POST['password']);

?>

This should do it.

answered Dec 08 '11 at 18:54

Griffin

644
6
18

Just one question, what does mysql_real_escape_string actually do? – H Bellamy Dec 08 '11 at 18:57
@HBellamy, it escapes certain characters and is strongly advised to be used whenever you send data to a database. Read more [here](http://php.net/manual/en/function.mysql-real-escape-string.php). – Griffin Dec 08 '11 at 18:59
That grave delusion again. When it will be wiped from this site? – Your Common Sense Dec 08 '11 at 19:32
@Col.Shrapnel An elaboration would be nice. – Griffin Dec 08 '11 at 19:37
1

`$fieldname = "user;drop table users"; $fieldname = mysql_real_escape_string($_POST['fieldname']); $sql = "SELECT * FROM table ORDER BY $fieldname";` enough? – Your Common Sense Dec 08 '11 at 19:41

Your Common Sense · Answer 5 · 2011-12-08T19:55:29.647

0

is there a way to detect that the user is trying to inject bad code?

It is useless and wrong approach.
You have to format your data properly, not hunt for some odd codes.

Besides that, I have a feeling that your problem cannot be solved with prepared statements.
If so, you will find the solution in my answer in the question linked in the comments.

edited Dec 08 '11 at 19:55

answered Dec 08 '11 at 19:26

Your Common Sense

156,878
40
214
345

How to prevent mysql injection in Php before it is submitted to the database

5 Answers5