0

I'm trying to search a MySQL database using PDO prepared statements as such...

try
    {
    // Prepare PDO Statement
        $stmt = $readdb->prepare("SELECT * FROM athletes WHERE :search LIKE :term");

    // Bind
        $stmt->bindValue(':search', $search);
        $stmt->bindValue(':term', '%' . $term . '%');

    // Execute
        $stmt->execute();
    }

...this produces no error, however also returns no results.

For comparison sake, if I bypass binding by directly inserting my variables into the statement, it does work...

try
    {
    // Prepare PDO Statement
        $stmt = $readdb->prepare("SELECT * FROM athletes WHERE $search LIKE '%$term%' ");

    // Execute
        $stmt->execute();
    }

...but that obviously is a big security hole.

I'm almost certain this is some kind of syntax mistake I'm making, but after spending over two hours on it, I'm hoping fresh eyes may help.

Is there anything glaringly obvious I'm missing here? Thanks much, Stack!

Faesal
  • 679
  • 6
  • 19
Shaun of the Dead
  • 59
  • 1
  • 6
  • 3
    You can not bind your column name as a parameter. Check this question for more [info](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) – Faesal Dec 16 '21 at 00:50
  • Thanks @Faesal, but I'm not sure I follow. Do you mean it's actually the `:search` part of my statement which is incorrect because it's referencing a certain database column? EDIT: Yes, yes you did. I just tested. Thanks for putting me on the correct path, this was driving me mad! – Shaun of the Dead Dec 16 '21 at 00:57

1 Answers1

1

For the sake of others having a similar issue who may stumble upon this thread, I'm pasting my final solution below, based on @Faesal's comment, "You can not bind your column name as a parameter."

try
    {
    // Prepare PDO Statement
        $stmt = $readdb->prepare("SELECT * FROM athletes WHERE $search LIKE :term");

    // Bind
        $stmt->bindValue(':term', '%' . $term . '%');

    // Execute
        $stmt->execute();
    }

$search would need to be sanitised.

Thanks again for your help!

Shaun of the Dead
  • 59
  • 1
  • 6
  • `$stmt = $readdb->prepare("SELECT * FROM athletes WHERE $pdo->quote($search) LIKE :term");` – Shaun of the Dead Dec 16 '21 at 01:20
  • 1
    Did you ever try this $pdo->quote($search) stuff? Which a) doesn't work written this way and b) won't work anyway, even if you manage to apply quote() to $search correctly, simply because quote does exactly the same job as bindValue does. What you need is a white list filtering, *as shown in the correct answer you were directed to*, which serves incomparably better "for sake of others" – Your Common Sense Dec 16 '21 at 03:11
  • @ShaunoftheDead Also, just to note, if you have to use a variable for a column name, that _generally_ means you have a poor database set up. You pretty much should avoid this if at all possible. If you absolutely _must_ do this, if the column comes from the client (such as a dropdown or input box from the users side), then you should verify that the column they use is actually valid. The client can manipulate these inputs and return anything that they want. If it's supplied from the server, it's not as big of an issue. – GrumpyCrouton Dec 16 '21 at 16:54
  • Thanks, @GrumpyCrouton! Yes, there's actually a check further up the script which excludes all but a narrow collection of desired values, with anything else rejecting immediately to error. The added note about sanitation is just a redundancy. – Shaun of the Dead Dec 16 '21 at 17:52