How to safely embed code in mySQL database

Question

I am creating a website where users can store code snippets using PHP and mySQL database. But I can’t figure out how to safely insert user-entered code into my database. I cannot convert the input with the security functions that I usually use ( trim, stripslashesetc.), because the thing is that you can view the code entered into the database. I looked at my_real_escape_string(), but I saw that it does not come out of %and _that can be used as MySQL wildcards. Is this a threat, or can I just use it my_real_escape_string? Thanx in advance.

Answer 1

Wildcards work only when used in SELECTqueries, and then only when using certain functions. Therefore mysql_real_escape_string(), it will be convenient to use code to insert it , since they will have no effect.

To make it better, I would recommend using PHP PDO so you can use parameter binding. The following example is from a 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

Use mysql_real_escape_string()provides full protection for string values. The fact that the data cannot be %and _does not matter, they do not pose a security risk.

For int values, you need to either check if they are actually numbers, or enclose them in quotation marks:

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

mysql_real_escape_string() !

, @ . , , PDO. , mySQL, , SQL- .

Answer 3

Use the parameterized insert statement. In fact, use parameterization even for your selections and updates.

It will automatically process these things for you.

Answer 4

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));

Answer 5

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

<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>