Searching with multiple variables PHP/Mysql

Question

I'm trying to create a search where the column and value are both variables using prepared statements. The query is where I need help.

$column=$_POST['filter'][0][columnName];
$value = trim($_POST['filter'][0][value]);

$stmt = $mysql->prepare("select * from TABLE WHERE $column like '%$value%'");

I want to be able to specify different column and value pairs to use in conjunction with eachother- so that i can search column one for one value and also column two for another value.

thanks!

Answer 1

I don't think PHP's mysql library has prepared statements. Is your $mysql variable actually a mysqli or pdo connection object?

With mysqli, your code would look like this:

$dbconnection = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
$stmt = $dbconnection->prepare("select * from TABLE WHERE $column like '%?%'");
$stmt->bind_param('s',$value);

There's no way to bind a parameter for a column name, which means that you're stuck inserting $column directly into your query. Doing that with any value the user passed you in POST is a bit of a security hole. Since it's constrained to be the same as one of your column names, I'd suggest validating it first to make sure it's not set to something different.