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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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 "