parameters in MySQLi

Question

Hi -

I'm using PHP with MySQLi, and I'm in a situation where I have queries like

SELECT $fields FROM $table WHERE $this=$that AND $this2=$that2

So far I've written some code that splices up an array that I give it, for example:

$search = array(name=michael, age=20) //turns into
SELECT $fields FROM $table WHERE name=michael AND age=20

Is there a more efficient way to do this?

I'm rather worried about MySQL injections - this seems very vulnerable. Thanks!

Answer 1

Oddly enough, the title to your question is basically the answer to it. You want to do something like this, using mysqli parameterized queries:

$db = new mysqli(<database connection info here>);
$name = "michael";
$age = 20;

$stmt = $db->prepare("SELECT $fields FROm $table WHERE name = ? AND age = ?");
$stmt = bind_param("si", $name, $age);
$stmt->execute();

More information in the mysqli section of the manual, specifically the functions related to MySQLi_STMT.

Note that I personally prefer using PDO over mysqli, I don't like all the bind_param / bind_result stuff that mysqli does. If I have to use it I write a wrapper around it to make it work more like PDO.

Answer 2

Look into parameterized queries.

I'm sure this question has been asked many times here, as well.