Talg123 Talg123 - 1 year ago 45
PHP Question

PDO fetchAll as class problems

Ok since Ive tried for SO long and couldnt find my problem Id like to get some help.

so here is my code:

function Search($str,$amount,$start)
{
$db = new Database();
$products=array();
$arr=[$str,$str];
$sql="SELECT * FROM products WHERE P_name LIKE %?% OR Description LIKE %?% LIMIT $amount OFFSET $start";
$result = $db->dataB->prepare($sql);
$result->execute($arr);
$l = $result->fetchAll(PDO::FETCH_CLASS,'Products');
return $l;
}


and it just wont work, Ive tried so many things but nothing worked for me.
plz some help :(

the error message:


Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%'s'% OR Description LIKE %'s'% LIMIT 9 OFFSET 0' at line 1 in C:\xampp\htdocs\project\class\Functions.php:382 Stack trace: #0 C:\xampp\htdocs\project\class\Functions.php(382): PDOStatement->execute(Array) #1 C:\xampp\htdocs\project\search.php(57): Search('\xD7\xA9', 9, 0) #2 {main} thrown in C:\xampp\htdocs\project\class\Functions.php on line 382
Blockquote


Basicly I want to bind so I wont get any sql injections and for some reason it wont work for me tnx in advance.

Answer Source

The are few problems with your code:

  1. Problem is related to the SQL LIMIT clause. When in emulation mode (which is on by default), PDO substitutes placeholders with actual data, instead of sending it separately. And with "lazy" binding (using array in execute()), PDO treats every parameter as a string. As a result, the prepared LIMIT ?,? query becomes LIMIT '10', '10' which is invalid syntax that causes query to fail.

You have two options here ;

one is turning emulation off (as MySQL can sort all placeholders properly). To do so one can run this code:

$db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

and parameters can be kept in execute()

Another way would be to bind these variables explicitly while setting the proper param type.

  1. second problem : $sql="SELECT * FROM products WHERE P_name LIKE %?% OR Description LIKE %?% LIMIT $amount OFFSET $start";

The above statement will produce an error for the following reason :

A placeholder have to represent a complete data literal only - a string or a number. And by no means can it represent either a part of a literal or some arbitrary SQL part. So, when working with LIKE, You have to prepare our complete literal first, and then send it to the query the usual way

This is how your full code should look : I'm not sure what dataB in $result = $db->dataB->prepare($sql); does not sure if its should be there so I have left it out.

<?php

function Search($str,$amount,$start)
{
    $db = new Database();
    $products=array();
    $str = "%$str%";
    $sql="SELECT * FROM products WHERE P_name LIKE ? OR Description LIKE ? LIMIT ? OFFSET ? ";
    $result = $db->prepare($sql);
    $result->bindParam(1,$str,PARAM_STR);
    $result->bindParam(2,$str,PARAM_STR);
    $result->bindParam(3,$amount,PARAM_INT);
    $result->bindParam(4,$start,PARAM_INT);
    $result->execute();
    $l = $result->fetchAll(PDO::FETCH_CLASS,'Products');
    return $l;
}

?>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download