How to safely insert code in mySQL database

Question

I'm building a website where users can store code snippets, using PHP and a mySQL database. But I can't figure out how to safely insert user inputed code into my database. I can't transform the input with the 'safety' functions I normally use (trim, stripslashes, etc.) because the whole point is that you can view the code as it's inputed in the database. I've looked at my_real_escape_string() but I saw that it does not escape the % and _, which can be used as MySQL wildcards. Does that pose a threat, or can I just use my_real_escape_string? Thanx in advance.

Answer 1

Wildcards only take effect when used in SELECT queries and then only when using certain functions. So for inserting the code it will be fine to use mysql_real_escape_string() as they will have no effect.

To make it better I would recommend that you use PHPs PDO so that you can use parameter binding. The following example is from the PHP manual:

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

Answer 2

Using mysql_real_escape_string() provides complete protection for string values. The fact that there may be % and _ in the data is not relevant, they do not pose a security threat.

For int values, you need to either validate whether they actually are numbers, or wrap them into quotes:

$intValue = mysql_real_escape_string($_POST["intValue"]);
$query = mysql_query("INSERT INTO table SET intValue ='$intValue'"); 
                                                        // note the quotes

Without the quotes, mysql_real_escape_string() is useless on numeric values!

However, as @Daniel A. White already said, if you are just starting out, consider using the PDO library instead. It's newer than the old mySQL functions and offers parametrized queries that, if used properly, make SQL injection impossible.

Answer 3

Using PDO:

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('INSERT INTO fruit(name, colour, calories) VALUES(?, ?);');
$sth->execute(Array($calories, $colour));