I have certainly participated in numerous discussions that involve prepared statements. I apologize if it seems like we are rehashing things. I suppose the benefits, aside from syntactical ones and automatic escaping, have never really sunk in to my sluggish brain and I've only just been asymptotically approaching true understanding.
In my estimation, the 'higher efficiency' benefit for multiple-query situations has never seemed especially important inasmuch as queries inside a loop are strongly discouraged. I've always striven to avoid looping query situations whenever possible -- here evidenced by my use of UNION to roll the numerous (dozens of?) queries in this search function into a single query. I find myself wondering what performance/efficiency benefit, if any, prepared statement caching would bring to such a query and can't think of any.
As for the automatic escaping with prepared statements, it never sank in that this type of escaping would somehow be superior to the use of PDO::quote or mysqli_real_escape_string. Looking now at the docs on mysqli_real_escape_string, I'm more than a bit horrified by this little description:
Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z.
Is that ALL this function does is change those characters? Is that adequate to prevent sql injection? Given the SQL injection attempts I've seen in my apache logs, this seems dangerously inadequate, but I don't fully understand how various SQL engines interpret quotes, DELETE chars, cursor-moving chars, and all that sort of thing.
Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries
I had seen this -- and I may be paranoid -- but I'm very suspicious of this claim of immunity and believe that relying on this automatic escaping to be far too credulous. I'm curious what sort of logic/mechanism might insure this 'immunity' but dread the prospect of trying to read source code for MySQL, PostGreSQL, etc. I'd also point out that PHP's PDO sometimes just emulates real PDO behavior, depending on how one has installed the MySQL client. Furthermore, application-level validation can check if your data matches application-level requirements. E.g., the DBMS engine's value escaping won't check if the supplied email address is valid before cramming it into a VARCHAR field.
Given that I only need a simple text search function and given the current implementation (which everyone should feel free to criticize), my decision to use preg_replace to scour the user input string seems reasonable given all these considerations. I know I still need to use PDO::quote or prepared statements on these keywords but given that they need to be escaped not just for the DB but also escaped for use in a REGEXP pattern within the SQL generated (does any escape function for exist for this purpose?) I thought it reasonable to seek out some sage input from my esteemed colleagues. Only in preparing this post and responding to your input (which I value very much) have I (re)discovered limitations in CodeIgniter and in PHP's PDO implementation.
And Weedpacket is entirely right that I should work up some unit/regression tests. My skills in this realm are woefully inadequate. I realized I've avoided lots of tedious SQL work for years now thanks to the convenience of a system I've constructed which lets me interact with DB_x objects (PHP classes) to insert records rather than writing SQL for this purpose. I'm only aware today that this system may rely on some false assumptions as far as escaping goes.