0

for some reason my PDO prepared statement wasn't working on a particular part of a query but working else where in the same query. Whether I tried ? or :name to substitute the query information, it wasn't working. Maybe there is a bug...? Anyway, to get my query to work I have changed my query to 

$sql = 'select ifnull('.$var.',0) from table';

instead of 

$sql = 'select ifnull(?,0) from table';

or

$sql = 'select ifnull(:name,0) from table';

So I have concatenated the variable in the middle. My code is now working and I'm happy to continue with it this way because the variable is only defined by me, not user input defined so no worry of sql injection.

I am just wondering if anyone else is having to do this? Or if anyone can suggest why it wasn't working right before? Or even if there some obvious problem with this?

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
Cat
  • 34
  • 6
  • 2
    I think you can only use parameters as *values*, not as field/table names. – gen_Eric Oct 22 '14 at 19:15
  • 2
    The issue you're having is that the first param of `IFNULL()` is a _column name_, not a value. You cannot bind a column name as a parameter. Instead you need to validate it as a member of an array of acceptable values. The question I linked has a number of examples of how to use `in_array()` as a whitelist for acceptable variable values. Then it is safe to use the variable `$var` in that context. – Michael Berkowski Oct 22 '14 at 19:16
  • Like `if (in_array($var, array('col1','col2','col3')) { // safe to execute with $var }` – Michael Berkowski Oct 22 '14 at 19:17

0 Answers0