Escaping ‘%’ in MySQL LIKE statement when sprintf

by Muneer Shaheed 01 Nov 0 Comments

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…

1
2

$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,

1
2

$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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

$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);
delicious digg reddit facebook technorati stumbleupon savetheurl

Leave a Reply

Please wrap all source codes with [code][/code] tags.
movie downloads  download wordpress themplates