laserlight
I apologize if I did not make reference to your elaboration, which I appreciate very much. I did in fact read it several times and had difficulty understanding the first half. It was the latter half that helped me to comprehend the actual mechanism by which prepared statements prevent SQL injection. Only you calling me out here brought me to understand that I was paraphrasing what you already explained. Your further elucidation has made it even clearer and I am even deeper in your debt. Thank you for the additional detail about byte code et. al. My reading comprehension skills are not especially sharp sometimes and I apologize if I seem obtuse or ungrateful. Also -- and this may be an irritating habit -- paraphrasing the descriptions of others really helps me to solidify my understanding of complicated concepts.
Regarding my application-level validation, I'm stripping out everything (or trying to anyway) except letters, numbers, and spaces from the user search string. This serves two purposes (albeit in a ham-fisted way): 1) it should prevent SQL injection arguably more effectively than the dubious stack of PDO emulation and framework code I'm building on and 2) should eliminate the need to worry about escaping any special REGEX chars like asterisks or square brackets or $ or ^ or whatever.
No one has commented much on the SQL-generating code I posted which makes use of the word-end boundary markers ([[:<:]] and [[:>:]]). My regex cleanup in PHP should still allow users to search for actual words and numbers so it still supports fundamentally useful functionality and at the same time I need not face the task of sorting out how to escape my keywords and/or regex expressions in order to preserve the original keywords while still providing safe, functional code.
I guess you could say that one of my application requirements is "search the four different columns in the four different tables while preserving the the original keywords, my word-boundary logic, and the relative scoring of the tables and full matches versus partial matches" I'm concerned that the word-boundary regex I have (which does a fair job on our production server) will just run into trouble if we start allowing more punctuation into the SQL. I'm entirely open to other methods of full text search, but have no experience with them. If anyone wants to suggest something, I'd be grateful. I appreciated Weedpacket's sound-alike suggestion very much but this sounds like next-level enhancement. I'm mostly concerned about performance and precision at this point.