title: MySQLi prepared statements vs queries
body: <p>It seems like every question on this topic is the difference between mysqli prepared statements and straight mysqli queries in PHP, but nothing on what to do when prepared statements don't meet your needs. </p>
  
  <p>A prepared statement is certainly the way to go when performing a simple query:</p>
  
  <pre><code>$stmt = $connection-&gt;prepare("SELECT * FROM my_table WHERE id = ?");
  </code></pre>
  
  <p>But what about when things get more complicated? From the PHP manual:</p>
  
  <blockquote>
    <blockquote>
      <p>However, [markers] are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement, or to specify both operands of a binary operator such as the = equal sign.</p>
    </blockquote>
  </blockquote>
  
  <p>This becomes a problem with complicated queries that <em>do</em> need to specify both operands of a binary operator, or some of the other restrictions that <code>mysqli_prepare</code> has.</p>
  
  <p>In my case, I need to perform some queries to return results for blog entries (This is a simplified example, my connection variable is actually a private property of a blog class, but you get the idea):</p>
  
  <pre><code>$query = $connection-&gt;query("SELECT * FROM my_table WHERE $field = '$search'");
  </code></pre>
  
  <p>In this example, the <code>$field</code> variable is what column to search by, and the <code>$search</code> variable is what to search for. This type of query is not possible with prepared statements.</p>
  
  <p>I've done a lot of careful planning for functions such as these, and since I know that there are only X amount of columns to search by, I use conditionals to check that <code>$field</code> is equal to one of those columns, and <code>mysqli_real_escape_string</code> to escape any possible quote characters. But is this good practice? Based on what I've read and answers here on SO, you should <em>always</em> use prepared statements, but I have never seen complicated queries in those examples. Is there a better way to prevent SQL injection, a more advanced way to use prepared statements, or should I just stick to very careful validation here?</p>