Searching using PDO

Question

So I'm trying to execute a search using PDO. I have this search set up:

echo "<form action = 'user.php?search=yes' method = 'post' id='searchform'>
<a href='user.php?newuser=yes'>Add New User</a> || Search By
<select name = 'paramet' form = 'searchform'>
<option value = 'userID'>User ID</option>
<option value = 'firstname'>First Name</option>
<option value = 'lastname'>Last Name</option>
<option value = 'email'>E-Mail</option>
<option value = 'mobileno'>Mobile Number</option>
<option value = 'homeno'>Home Number</option>
</select>
<select name = 'howso' form = 'searchform'>
<option value = 'contains'>which contains</option>
<option value = 'equalto'>which is equal to</option>
</select>
<input type = 'text' name='criteria' required>
<input type = 'submit' value='Search'>
</form>

And then this handling the query:

{
$param = $_POST['paramet'];
$how = $_POST['howso'];
$crite = $_POST['criteria'];
if($how == 'contains')
{
$query = $hsdbc->prepare("SELECT * FROM user WHERE :param LIKE :crite");
$query->bindParam(':param', $param);
$query->bindValue(':crite', '%' . $crite . '%');
$query->execute();
}
else{
$query = $hsdbc->prepare("SELECT * FROM user WHERE :param = :crite");
$query->bindParam(':param', $param);
$query->bindParam(':crite', $crite);
$query->execute();
}

I'm getting no-where near the correct results. Any help?

placeholders can only represent values, not field/table names. You'll have to manually insert the relevant field names, and be wary of [SQL injection attack](http://bobby-tables.com) vulnerabilities. — Marc B, Apr 10 '14 at 20:39
http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991422 — Your Common Sense, Apr 10 '14 at 20:39
Hmm. If I have a limited number of columns, would it be possible to do it through a sequence of if statements? i.e. if $param = 'firstname' then the query would be "SELECT * FROM user WHERE firstname = :crite" I know it'd be extremely inelegant — user3508084, Apr 10 '14 at 20:42

Ray · Answer 1 · 2014-04-10T20:44:00.577

You can't bind column names. The best you can do is add the name to a white list array or something and insert it manually.

   if(in_array($param, $good_params_array)) {
         $query = $hsdbc->prepare("SELECT * FROM user WHERE $param LIKE :crite");
         $query->bindValue(':crite', '%' . $crite . '%');
         $query->execute();
   }

I've seen people query the DB for the table description to get the columns to see if the column name is listed, but that requires an addition DB request. Also, you might want to limit the fields they can search against

Searching using PDO

1 Answers1