PDO bindparam error when trying to execute

Question

0.00/5 (No votes)

See more:

Hi Everyone,

To my next question, PDO bindparam is showing an error:

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 '' ' order by 'emp_no' 'asc' limit 0, 10' at line 1 in C:\xxx\ajaxfile.php:41 Stack trace: #0 C:\xxx\ajaxfile.php(41): PDOStatement->execute() #1 {main} thrown in C:\xxx\ajaxfile.php on line 41

What I have tried:

$stmt = $con->prepare("select * from employees WHERE 1 ? order by ? ? limit ?, ?");

$stmt->bindParam(1, $searchQuery, PDO::PARAM_STR);
$stmt->bindParam(2, $columnName, PDO::PARAM_STR);
$stmt->bindParam(3, $columnSortOrder,PDO::PARAM_STR);
$stmt->bindParam(4, $row,PDO::PARAM_INT);
$stmt->bindParam(5, $rowperpage,PDO::PARAM_INT);

$stmt->execute();
$data1 = $stmt->fetchAll();

Posted 27-Dec-18 3:18am

Member 14093672

Updated 27-Dec-18 10:05am

Add a Solution

Comments

Member 14093672 27-Dec-18 9:31am

If i try this:

$stmt = $con->prepare("select * from employees WHERE 1" ? "order by" ? ? "limit" ?, ? );

I get this error.

Parse error: syntax error, unexpected '?' in

Richard MacCutchan 27-Dec-18 10:46am

What is "WHERE 1" supposed to mean?

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chris Copeland · Answer 1 · 2018-12-27T10:06:00

Solution 1

Remember that when binding parameters like this, the database adaptor will escape whatever values have been placed in the query. In this example, you're attempting to bind $searchQuery, $columnName and $columnSortOrder as actual SQL values. This won't work, as the binding will escape them (in this case, surrounding them with single quotes.)

What you're expecting:

select * from employees where 1 $searchQuery order by $columnName $columnSortOrder limit $row, $rowperpage

What's happening due to the escaping:

select * from employees where 1 '$searchQuery' order by '$columnName' '$columnSortOrder' limit $row, $rowperpage

Instead, ensure that you're populating the 3 string variables yourself (so you're guaranteed that there'll be no SQL injection) and then simply place them in the string part of the method:

select * from employees where 1 $searchQuery order by $columnName $columnSortOrder limit ?, ?

Also, as Richard mentioned, having where 1 is a little odd.

Posted 27-Dec-18 10:06am

Chris Copeland

Comments

Member 14093672 27-Dec-18 17:31pm

First of all, thank you Chris and Richard, and the following works perfectly o.k.

1.
Hide Copy Code

<pre>$stmt = $con->prepare("select * from employee where 1 $searchQuery order by $columnName $columnSortOrder limit ?, ?");$stmt->bindParam(1, $row,PDO::PARAM_INT);$stmt->bindParam(2, $rowperpage,PDO::PARAM_INT);

2. The total record count works o.k aswell.

Hide Copy Code

$totalRecordwithFilter = $con->query("SELECT count(*) from employee WHERE 1 $searchQuery")->fetchColumn();

However, in regards to WHERE 1 looks odd , it was trial and error and i forgot where I got this WHERE 1. Apologies for that, but the moment you take the "1" OUT from WHERE
clause, the query stops working and i am getting the following error? not sure why, its bit strange and ODD for me aswell.

Hide Copy Code

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 '' at line 1 in C:\fetch.php:27 Stack trace: #0 C:\fetch.php(27): PDO->query('SELECT count(*)...') #1 {main} thrown in C:\fetch.php on line 27

and the line 27 is .. without where 1

Hide Copy Code

$totalRecordwithFilter = $con->query("SELECT count(*) from employee WHERE  $searchQuery")->fetchColumn();

Chris Copeland 3-Jan-19 11:21am

Sorry for not getting back sooner on this, I was busy over December! It's likely that $searchQuery can be empty or null, and therefore the query would end with " WHERE ", which is invalid. Ideally you'd want to check whether the search query actually has a value, and perhaps adjust the content accordingly:

if ($searchQuery != '') {  $searchQuery = ' WHERE '.$searchQuery;}

You can then use that in your query which would only include the clause if it was populated. If you were to leave it is a "WHERE 1" then the $searchQuery variable would need to begin with " AND "

PDO bindparam error when trying to execute

1 solution

Solution 1

Add your solution here

Preview 0

PDO bindparam error when trying to execute

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us