bindParam seemingly not binding paramaters

[multichild]

I have spent all day on this so hopefully someone can spot the issue.

If I start with how it was and can show you the results

$limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'";
$query = str_replace('{limit clause}', $limitClause, $query);

$r=$conn->prepare($query);
$r->bindParam(':fieldName', $field_name);
$r->bindParam(':operator', $operator);
$r->bindParam(':keyword', $keywordb);
$r->execute();

When I echo $r out I get this -

echo ($r->queryString);    
WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like 'DOM%') select COUNT(*) from LIMIT WHERE Contract_Number like 'DOM%'

That works fine, its when I then start using pdo that things stop working

Change it to -

$limitClause .= " :fieldName :operator :keyword";
$query = str_replace('{limit clause}', $limitClause, $query);
$keywordb = $keyword."%";
$r=$conn->prepare($query);
$r->bindParam(':fieldName', $field_name);
$r->bindParam(':operator', $operator);
$r->bindParam(':keyword', $keywordb);
$r->execute();

echo ($r->queryString);

I get which looks right

WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE :fieldName :operator :keyword) select COUNT(*) from LIMIT WHERE :fieldName :operator :keyword

But I dont get any results at all, it just doesn’t seem to recognise the binding, and so nothing appears

[droopsnoot]

I don’t think you can use a bound parameter to specify the operator. I’m also pretty sure you can’t use the same parameter name twice, even if you intend it to have the same value.

[multichild]

Hi droopsnoot, ye this has been very confusing, so you think it could be the ‘Contract_Number’ and ‘Like’ values do you?

When you say use the same paramater name twice, what are you pointing too sorry.

[droopsnoot]

This is what I mean by using the same parameter name twice:

WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' 
FROM Intranet WHERE :fieldName :operator :keyword) select COUNT(*) 
from LIMIT WHERE :fieldName :operator :keyword

though now I look at it, I’m not familiar with that kind of SQL syntax, which may be more a lack on my part. But you have the “:fieldname :operator :keyword” section twice. Could by a copy/paste thing in here.

Now I think about it, I don’t think you can specify either the Column Name or the Operator as a parameter. I had a look in the PDO documentation for something to back that up, but I can’t see anything now.

[multichild]

I think in the binding it could also be adding apostrophes and that’s breaking it, so as a quick fix I’m going to revert to below, which will check the value of field_name and empty it if its value isn’t what it should be.

We are moving everything over to c# using the mvc framework next year, this only come about as a security issue that we found, so hopefully below will resolve it so I can move on.

if ( !in_array($field_name, array('Contract_Number','Database ID','Contract_Number','Contract_Status','Contract_Start','Contract_End','Contract_Length','Renewal_Date','Site_Name','Street','City','Country','Region','Postcode','Group_Member','Group_Name','No_of_Rooms','Type_of_Establishment','CSF','Food_Safety','Swimming_Pool_Safety','Legionella_Safety','Room_Safety','Fire_Safety','Hurricane','Green','Guest','Quality_Safety','Supply_Safety','Tourcheck','No_of_Modules','Currency_of_Invoice','Annual_Contract_Value','No_of_audits_per_annum','No_of_visits_per_annum','Seasonal_or_Full','Month_Opens','Month_Closes','Invoicing_Profile','contract_File','date_Created','date_Modified'),true)) {
$field_name = "";
} else { }

Because its value can only be something thats selected form the dropdown, so its easily managed.

[droopsnoot]

The closest I’ve got to confirming whether or not you can use parameters for column names and operators is from phpdelusions: https://phpdelusions.net/pdo#bindable

@phpdelusions

In fact, the list is overwhelmingly short: only string and numeric literals can be bound. So you can tell that as long as your data can be represented in the query as a numeric or a quoted string literal - it can be bound.
…
and
…
a placeholder have to represent a complete data literal only - a string or a number namely. And by no means can it represent either a part of a literal or some arbitrary SQL part.

In this case, neither a column name nor an operator could be represented as a quoted string.

With a bit of luck, someone who knows for sure might comment.

[multichild]

having done that leaving :keyword on its own, it still doesn’t work and so it must be your point of having it appear twice, and it is deliberate.

[multichild]

Well droopsnoot, you where spot on mate, cheers.

I changed it around so rather than having :keyword twice, I made the second one slightly different to create 2 bindings and it worked, so it was the fact I had it twice.

Thanks