PDO prepared statements to store html content

Question

I'm looking for a way to handle HTML content within prepared statements.

My application provides a basic WYSIWYG Editor and after the user is saving the content my script stores HTML-Data in an sqlite database.

But if i'am using a prepared statement my HTML gets -naturally- escaped.

This is what i've so far:

try {

    /* Create databases and open connections */
    $dbh = new PDO( 'sqlite:db/coaching.sqlite' );

    /* Set Error Mode for Exception Handling */
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    /* Prepare SQL Statement */
    $query = $dbh->prepare( "UPDATE Content SET Value=:value WHERE Token=:token" );

    /* Bind Param to Statement */
    $query->bindParam( ':token', $_POST['id'], PDO::PARAM_STR);
    $query->bindParam( ':value', $_POST['value'], PDO::PARAM_STR);

    /* Execute Query */
    $query->execute();

    /* Echo Data */
    echo $_POST['value'];

    /* Close connections to Database */
    $dbh = NULL;

}
catch( PDOException $e ) {

    /* Print Error-Messages */
    echo $e->getMessage();
}

Are you sure it gets escaped by bindParam() and not when retrieving the HTML from the HTTP request? — Leven, Apr 18 '12 at 20:31
As far as i know the user input is automatically quoted within bindParam(). — gearsdigital, Apr 18 '12 at 20:37
@gearsdigital Prepared statements do not escape variables. The command and the variables are transferred to database simultaneously but independently. If you see your data escaped in database, there's another reason. E.g. `magic_quotes` are turned on. Can you echo `get_magic_quotes_gpc` in your script to see if they're On or Off? — Taz, Apr 18 '12 at 21:18
You're right. magic_quotes are enabled and if i disable it nothing gets escaped. But just for understanding: A prepared statement reduces the risk of SQL Injecions? Right? It's the first time i work with PDO and prepared statements. — gearsdigital, Apr 18 '12 at 21:35
@gearsdigital OK. I extended my comment - see my answer below. — Taz, Apr 19 '12 at 08:27

score 7 · Accepted Answer · edited May 23 '17 at 11:58

Prepared statements do not escape variables. The command and the variables are transferred to database simultaneously but independently. If you see your data escaped in database, there's another reason. E.g. magic_quotes are turned on. Can you echo get_magic_quotes_gpc in your script to see if they're On or Off? If they're On, you can set them Off using different techniques. This will solve the problem.

Additionaly, following your comment, prepared statements do prevent SQL injection attacks so you don't have to worry about escaping your variables. What may be difficult to understand is the way the prepared statements work. Say you have a query:

$query = "SELECT `id` FROM `users` WHERE `login` = '" . $login . "' AND `password` = '" . $password ."'";

$login and $password are passed to the query directly, as they are. If someone attempts to pass mylogin' -- to $login, the query becomes:

$query = "SELECT `id` FROM `users` WHERE `login` = 'mylogin' -- ' AND `password` = 'anypassword'";

and is send to the database. This way an attacker can gain access to any account.

What prepared statements do, they transfer the query arguments independly of the query. The query is NOT build of the variables before it is transferred to database. Instead, the variables are transferred somehow next to the query. They are referenced in the query. This way the query can't be spoofed neither intentionally nor unintentionally.

With prepared statement the exampled $login will be transferred as is, and will not affect the query structure.

If it would be possible to carry passengers by plane, with passengers actually NOT boarding the plane, that would be called a "prepared flight" :) Passengers would not be able to influence the route and hijack the plane. They would have appeared at the target airport with the plane landing.

I had the same issue with the `magic_quotes` on super globals `$_POST` and `$_GET`. I ended up just going to PHP 5.4 which does not have this "feature" — user17753, Nov 07 '13 at 17:29

Hajo · Answer 2 · 2012-04-19T08:52:35.363

2

have you tried to store the data encoded?

http://php.net/base64_encode

or http://php.net/serialize

should work if used before / after the db is used

edited Apr 19 '12 at 08:52

answered Apr 18 '12 at 20:29

Hajo

849
6
21

2

HTML is just text. There is no need to serialize before storing it in a database. – Leven Apr 18 '12 at 20:34

PDO prepared statements to store html content

2 Answers2

Linked