PDO showing error

2
votes

I have a query below which I did with mysql_query before and it executed properly.. But using PDO it's showing some error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1'

This is my code with mysql_query :

$sql1 = "SELECT * FROM product WHERE id IN (";
                    foreach($_SESSION['cart'] as $id => $value){
                        $sql1 .= $id.',';
                    }
                $sql1 = substr($sql1, 0, -1) .")";  
                $query = mysql_query($sql1);

Using PDO without prepare statement.. : 

$sql1 = "SELECT * FROM product WHERE id IN (";
                    foreach($_SESSION['cart'] as $id => $value){
                        $sql1 .= $id.',';
                    }

                $sql1 = substr($sql1, 0, -1) .")";

                $query = $db->query($sql1);
2
phpmysqlpdo
You should use prepared queries for this. Insert ?, bind parameters. - Brad
Magically changing mysql_ to PDO does not fix the fact that you are injecting variables directly into your query meaning it might fail (and is very insecure!). - h2ooooooo
I know that very well... Using prepared statement and bind parameter won't fix this problem.. I will prevent sql injection later - nick
I don't understand why one would take the thought process of "I will fix SQL injection later". Why not just code it right the first time? Why come back and have to refactor your code a second time when you will know you need to do it from the start? - Mike Brant
ignoring all the other problems with the code, why not just $sql = "..." . implode(',', array_keys($_SESSION['cart']))? Boom, instant proper number of commas. - Marc B

2 Answers

4
votes

You miss to "add" the string here:

$sql1 = substr($sql1, 0, -1);
$sql1 .=  ")";
3
votes

In the PDO tag (info) you will find the correct procedure for PDO Prepared statements and IN.

PDO Tag

The following code uses this method to add unnamed placeholders from your SESSION array

$in = str_repeat('?,', count($_SESSION['cart']) - 1) . '?';
$sql1 = "SELECT * FROM product WHERE id IN ($in)";
$params = $_SESSION['cart'] ;
$stmt = $dbh->prepare($sql1); 
$stmt->execute($params);

DEMO