Custom Search on a Single Field using PHP, SQL, grep

A client with over 1,000 different products needed to be able to search through the product codes using a partial string search. The built-in Drupal search engine does not support this at all. Apache Solr does better, but it is too heavyweight and does not fully support partial string searches.

What was needed was essentially to be able to "grep" a single field.

The solution was to create a custom search block, using a node-as-block.

<form  action="http://fashion-resources.com/sku-search-landing-page" method="get"> 
SKU Search: <input type="text" maxlength="128" name="sku_search_string" id="edit-search-block-form-1" size="15" value="" title="Enter the SKU string you wish to search for." class="form-text" /> 

The form above sends the search string that the user enters to a node named "sku-search-landing-page". This node simply uses PHP to call a shell script on the host computer with the search string as its argument.

<?php
$command = '/usr/local/bin/sku-search '. $_SERVER["REQUEST_URI"]; 
$specials = array(";", "&", "`", "|", ">", "<");
$command = str_replace($specials, "", $command);
$output = shell_exec($command);
echo "$output";
?>

Note: Be sure the Input format for this node is set to PHP code.

The actual script that runs on the host computer uses SQL to extract the necessary fields, inserts some HTML formatting and then runs the results through grep.

export string=`echo $1 | sed 's/.*=//'`
(
mysql -uuser -ppassword fashion<<EOF
SELECT node.nid AS nid, uc_products.model, node_type.name FROM node node LEFT JOIN uc_products uc_products ON node.vid = uc_products.vid LEFT JOIN node_type node_type on node.type = node_type.type ORDER BY uc_products.model ASC
EOF
) | 
awk -F"	" '
{
	print "<a href=http://fashion-resources.com/node/" $1 ">" $2 "</a>, " $3 "<br>";
}' | 
egrep -i ">.*$string"

Whenever user-entered data is passed to a shell script, alarm bells should go off about security issues. In theory, a clever user could write a carefully crafted search string using back quotes or other special characters which are interpreted by the shell. Search strings like the following could in theory cause problems:
` rm -rf / `
some text; rm -rf /

The PHP str_replace command is used to remove shell-specific special characters from the search string.

Another security concern is SQL injection. In our script (listed above), the code passed to SQL is static and contains no user-generated data, eliminating the possibility of SQL-injection.

An alternative approach for implementing search, which would pass the search string to the SQL "LIKE" operator, would have opened the door to SQL injection attacks.