# How can I improve this PHP code which builds an SQL query?

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
WHERE $namecondstr AND$citycondstr
AND $itemnocondstr AND$ordernocondstr
AND $serialcondstr ORDER BY$sortstr, Licenses.LicenseID";

-

## 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.
-
I use mysql_real_escape_string() whenever I get a parameter, I thought this would save me from SQL injections? –  Felix Dombek Nov 29 '12 at 6:53
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. –  Paul Nov 29 '12 at 7:15
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. –  Corbin Nov 29 '12 at 16:22
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_.... –  Felix Dombek Dec 4 '12 at 12:35