php - MySQLi prepared statements vs queries -

php - MySQLi prepared statements vs queries -

it seems every question on topic difference between mysqli prepared statements , straight mysqli queries in php, nil on when prepared statements don't meet needs.

a prepared statement way go when performing simple query:

$stmt = $connection->prepare("select * my_table id = ?");

but when things more complicated? php manual:

however, [markers] not allowed identifiers (such table or column names), in select list names columns returned select statement, or specify both operands of binary operator such = equal sign.

this becomes problem complicated queries do need specify both operands of binary operator, or of other restrictions mysqli_prepare has.

in case, need perform queries homecoming results blog entries (this simplified example, connection variable private property of blog class, idea):

$query = $connection->query("select * my_table $field = '$search'");

in example, $field variable column search by, , $search variable search for. type of query not possible prepared statements.

i've done lot of careful planning functions such these, , since know there x amount of columns search by, utilize conditionals check $field equal 1 of columns, , mysqli_real_escape_string escape possible quote characters. practice? based on i've read , answers here on so, should always utilize prepared statements, have never seen complicated queries in examples. there improve way prevent sql injection, more advanced way utilize prepared statements, or should stick careful validation here?

yes , no: necessary check $field variable agains white-list - way prevent sql injection - there no point in using mysqli_real_escape_string on $field variable. if column name reserved word or starts illustration number, should quote in backticks it.

you should still utilize prepared statement $search variable, although here mysqli_real_escape_string (instead of prepared statement, not both).

php security mysqli prepared-statement