Escaping '%' in MySQL LIKE statement when sprintf

Submitted by muneer on Sun, 11/01/2009 - 12:01
 
 
 
Printer-friendly version

I wanted to run a SQL query against MySQL database server which contains search string and need to be formated using sprintf. The problem arise when format. It is because of the sign “%” am using to advance my search term. The query was…

$sql = "SELECT * FROM user WHERE country = '%s' AND fName LIKE '%s%' ORDER BY fName";
$sql = sprintf($sql, $country, $searchTerm);

Here where the error fires. Thanks God, I found the solution for it.

This can be handled simply as follows,

$sql = "SELECT * FROM user WHERE country = '%s' AND fName LIKE '%s' ORDER BY fName";
$sql = sprintf($sql, $country, "%" . $searchTerm . "%");

but for a query like below, where it need multiple formating due to it complex and dynamic generation, I managed to prepare like below and worked fine for me.

$fieldArray = array("$t1.id", "$t3.avatar", "$t1.login", "$t1.firstName", "$t1.lastName", "$t2.title", "$t1.email", "$t1.active"); 
 
$sql = "SELECT DISTINCT ";
$sql.= "$t1.id, $t3.avatar, $t1.login, $t1.firstName, $t1.lastName, $t2.title AS groups, $t1.email, $t1.active ";
$sql.= "FROM $t1, $t2, $t3 ";
$sql.= "WHERE $t1.id = $t3.userId AND $t3.userGroup = $t2.id ";
IF($searchField > 0 && $searchField < COUNT($fieldArray))
{
    $sql.= "AND " . $fieldArray[$searchField] ." LIKE '%s' ";
    $sql = sprintf($sql, "%" . $searchVal . "%");
}
 
IF($orderBy > 0 && $orderBy < COUNT($fieldArray))
{
    $sql = str_replace("%", "%%", $sql);
    $sql.= "ORDER BY %s %s ";
    $sql = sprintf($sql, $fieldArray[$orderBy], $orderStyle, $start, $count);
}
 
$sql = str_replace("%", "%%", $sql);
$sql.= "LIMIT %d, %d";
$sql = sprintf($sql, $start, $count);

Before generating the next %, need to double the current % where it will become single % after formated.

Note this

$sql = str_replace("%", "%%", $sql);

Tags:

Add new comment