Is using PDO prepared statement and htmlspecialchars enough to prevent XSS and SQL injection?

Question

For exemple in this code, is htmlspecialchar preventing XSS and is the PDO prepared statement preventing SQL injection ?

if(isset($_GET['search']) AND !empty($_GET['search']) AND $_GET['search'] != ' ') {
    $search = htmlspecialchars($_GET['search']);
    $searchArray = explode(' ',$search);
    var_dump($searchArray);

    $videos = $stdb->prepare('SELECT id, title, videoTime FROM videos WHERE title LIKE "%'.$search.'%" OR title LIKE "%'.implode("\" OR title LIKE \"%", $searchArray).'%" ORDER BY id DESC limit '.$start.','.$videosPerPage);
    $videos->execute();
    $totalVideos = $totalVideosReq->rowcount();
    $totalPages = ceil($totalVideos/$videosPerPage);

    $currentPage = 1;
    if(isset($_GET['page']) AND !empty($_GET['page']) AND $_GET['page'] > 0 AND $_GET['page'] <= $totalPages) {
        $_GET['page'] = intval($_GET['page']);
        $currentPage = $_GET['page'];
    } else{
        $currentPage = 1;
    }
}

Your code is vulnerable to SQL injection attacks. Please learn to actually use prepared statements. — tereško, May 13 '17 at 18:22
Take a look here: http://php.net/manual/en/security.database.sql-injection.php — Bast, May 13 '17 at 18:41

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

You're calling prepare(), but just calling prepare() is not a magical way to protect from SQL injection.

You are still copying unsafe request data into your SQL query, without using parameters. This is how SQL injection happens.

The protection is to use parameters. This also requires that you use prepare() and execute(), but the point you should learn is that it's the parameterization that protects you, not the prepare().

I want you to understand this, and I would advise you not to put your code on any public web site until you do understand it.

Read the good description in the accepted answer to How can prepared statements protect from SQL injection attacks?

P.S.: You're also using htmlspecialchars(). This is no protection against SQL injection. Using htmlspecialchars() is helpful to protect against a different security risk, Cross-Site Scripting, but you do this when you want to echo output, not when you're writing an SQL query.

Thank you for your answer ! So now i understand that prepare() is for preparing your query so you can execute() it multiple time after . If i understand it well , to have a securized query i need to securize the variable(input) in the query ("$search" in this code). Is by puting : " $videos->bindParam(':search', $search); " and replacing $search by :search in the query could securize the variable ? — Jonathan Côté, May 13 '17 at 20:49

Is using PDO prepared statement and htmlspecialchars enough to prevent XSS and SQL injection?

1 Answers1