PDO Prepared Statement allows javascript to be executed

Question

I' am really confused with PDO Prepared Statement. I did everything as it says on php.net documentation. When I inserted JavaScript into the database and then query the database and output the results on the page, it gave me JavaScript alert which was on the database.

Does PDO protect user data from this type of attacks or do we have to do our own sanitizing and escaping or is it am missing something here.

<?php
    try {
        $db = new PDO('mysql:host=localhost;dbname=test', "root", "");
    } catch ( PDOException $e ) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
    }

    $query = $db->query('SELECT * from users');
    if( $query->rowCount() ){
        $rows = $query->fetchAll( PDO::FETCH_OBJ);
        foreach( $rows as $row ){
            echo $row->user_name;
            echo "<br/>";
        }
    } else {
        echo "No results found";
    }

    $user_name = "<script type=\"text/javascript\">alert(\"Works\");</script>";
    $user_email = "example@gmail.com";
    $user_password = "password";
    $user_status = "1";

    $data = array(
        ":user_name" => $user_name,
        ":user_email" => $user_email,
        ":user_password" => $user_password,
        ":user_status" => $user_status
    );
    $sql = "INSERT INTO users (user_name, user_email, user_password, user_status) VALUES(:user_name, :user_email, :user_password, :user_status)";
    $prepare = $db->prepare($sql);
    $exec = $prepare->execute($data);
?>

PDO doesn't sanitize anything. If you use prepared statements they provide a method to put data into an SQL database that is not susceptible to __SQL__ injection attacks. All other vulnerabilities are your responsibility to defend against. You should be using `htmlspecialchars()` or some variant thereof to encode your data for output. — , Oct 22 '14 at 02:55
Output escaping must be done context-specific (here `htmlspecialchars()` prior any `echo` statement), and it's not the domain of the database interface to handle. — mario, Oct 22 '14 at 02:57

score 3 · Accepted Answer · edited May 23 '17 at 12:20

When you use prepared statements and parameterize your SQL, PDO protects against SQL injection attacks.

But you're describing something called Cross-Site Scripting (XSS). This is a totally different security issue. It has no dependence on SQL or database content. You could create an XSS vulnerability with any application data, not limited to data that came out of a database. XSS, like SQL injection, is important for all programmers to learn about.

You've probably seen the highly-rated post about how to protect against SQL injection in PHP:

How can I prevent SQL-injection in PHP?

But here's a few good posts about XSS defense:

SQL injection and Cross-Site Scripting are consistently in the top two security mistakes that expose data on the web. See the OWASP Top Ten list of critical security risks: https://www.owasp.org/index.php/OWASP_Top_Ten_Project

Get yourself to that OWASP site. There are a lot of resources there to learn about security risks and how to solve them. And it's free!

I also like this wikipedia article ~ http://en.wikipedia.org/wiki/Secure_input_and_output_handling. And this more PHP specific one ~ http://lukeplant.me.uk/blog/posts/why-escape-on-input-is-a-bad-idea/ — Phil, Oct 22 '14 at 03:21

PDO Prepared Statement allows javascript to be executed

1 Answers1