5

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.

Ikhlak S.
  • 8,578
  • 10
  • 57
  • 77
Stefan Hagen
  • 658
  • 3
  • 11
  • 25
  • [`addcslashes`](http://php.net/addcslashes) is commonly used in addition to normal escaping or parameter binding to mask placeholders for `LIKE` expressions. – mario Jan 06 '12 at 14:09
  • trim/stripslashes are NOT safety functions. Extra space is never a concern in sql, unless you're worried about losing data in a fixed-length text/string field. stripslashes is a moronic braindead piece of garbage leftover from PHP's earlier days when magic_quotes was king of the land. – Marc B Jan 06 '12 at 14:37
  • previous StackOverFlow Questions that could answer your question: http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php http://stackoverflow.com/questions/4461759/parameterized-queries – John Woo Jan 06 '12 at 14:10

5 Answers5

6

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();
?>
Treffynnon
  • 21,365
  • 6
  • 65
  • 98
4

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.

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
1

Use a parameterized insert statement. Actually, use parameterized even for your selects and updates.

it will automatically handle these things for you.

Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
  • 2
    Not my downvote, but it's arguably not really an answer to the OP's question. Plus you're not explaining which library to use – Pekka Jan 06 '12 at 14:12
1

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));
jValdron
  • 3,408
  • 1
  • 28
  • 44
-1

This is what worked for me, [adscript] is a code snippet.

<textarea rows="6" cols="80"><input name="adscript" type="text/javascript" class="text-input textarea" id="adscript"  value="<?php echo htmlentities($row['adscript']);?>" size="80" height="40" maxlength="1000"></textarea>
user3691314
  • 159
  • 3
  • 5