Building an SQL query string

Question

How can I write this PHP code better? It puts together an SQL query string from user input. The task is to return search results based on one or more text fields. The user can determine if partial matches are allowed.

$compop = $allowpartial ? "LIKE" : "="; // use LIKE for wildcard matching
$any = $allowpartial ? "%" : ""; // SQL uses % instead of * as wildcard
$namecondstr = ($name === "") ? "TRUE" : ("Name $compop '$any$name$any'");
$citycondstr = ($city === "") ? "TRUE" : ("City $compop '$any$city$any'");
$itemnocondstr = ($itemno === "") ? "TRUE" : ("ItemNo $compop '$any$itemno$any'");
$ordernocondstr = ($orderno === "") ? "TRUE" : ("OrderNo $compop '$any$orderno$any'");
$serialcondstr = ($serial === "") ? "TRUE" : ("Serial $compop '$any$serial$any'");
$sortstr = ($name !== "") ? "Name" :
 (($city !== "") ? "City" :
 (($itemno !== "") ? "ItemNo" :
 (($orderno !== "") ? "OrderNo" :
 "Serial")));
$query = "SELECT * From Licenses
 LEFT JOIN Items
 ON Licenses.LicenseID = Items.LicenseID
 WHERE $namecondstr 
 AND $citycondstr 
 AND $itemnocondstr 
 AND $ordernocondstr
 AND $serialcondstr
 ORDER BY $sortstr, Licenses.LicenseID";

Answer 1

Use Prepared Statements

Your code is vulnerable to SQL injection attacks. Use PDO or mysqli prepared statements to avoid this. See this answer for how to use PDO for this. If you are using mysql_* you should know that it is already in the deprecation process.

Miscellaneous

• Consider using empty to check for empty strings (see comment from Corbin below).
• Personally I would use an if else rather than have two identical ternary conditions.

• Felix Dombek: I use mysql_real_escape_string() whenever I get a parameter, I thought this would save me from SQL injections?
• Paul: I didn't see it in your code above and you said it came from user input, so I thought it needed to be said. I think mysql_real_escape_string is okay so long as you remember to use it everywhere that you need to. It will suck when mysql_* is no longer a part of core PHP due to deprecation though.
• Corbin: On a very pedantic note: it's worth mentioning that empty($x) and $x === "" are not equivalent. You of course never said that they are equivalent, but it could catch someone unfamiliar with empty by surprise.
• Felix Dombek: OK, I thought someone would comment on the (in my eyes pretty awful) chain of AND TRUE AND TRUE... terms if not all search criteria are specified by the user, or the rather dubious "Var $compop '$any$var$any'", but since there are no answers that tell me how to avoid those, I accept your answer. Thanks a lot for the deprecation note, will change to mysqli_....