mySQL LIKE statement in a function

quark76

I'm trying to convert the following statement:

$sql = "SELECT * FROM $tbl_name WHERE title LIKE '%".$search_term."%' OR description LIKE '%".$search_term."%' LIMIT $start, $limit";

To:

$sql = sprintf(
   "SELECT * FROM $tbl_name WHERE title LIKE '%s' OR description LIKE '%s' LIMIT $start, $limit",
   mysql_real_escape_string('%".$search_term."%'),
   mysql_real_escape_string('%".$search_term."%')
);

But I get nothing when I search. What am I doing wrong?

Thanks

bradgrafelman

Echo out the SQL query string and you'll see why. You're most likely going to find something like:

WHERE title LIKE '&#37;".foo."%'

I would suggest moving the % wildcards into the format string itself (don't forget to use '%%' to get a literal percent sign in a sprintf() format string!), then you'd simply be passing $search_term itself to [man]mysql_real_escape_string/man instead of the convoluted string you are now.

quark76

bradgrafelman;10956376 wrote:
Echo out the SQL query string and you'll see why. You're most likely going to find something like:
WHERE title LIKE '%".foo."%'
I would suggest moving the % wildcards into the format string itself (don't forget to use '%%' to get a literal percent sign in a sprintf() format string!), then you'd simply be passing $search_term itself to [man]mysql_real_escape_string/man instead of the convoluted string you are now.

Thanks! The echo'ing worked perfectly.

quark76

Actually, the echoing worked to show me what it says, but I can't get it to work. What I'm trying to do is search the content in the DB & the string above worked perfectly outside of sprintf, but I like the sprintf function that works great to control what input get inserted into my queries.

Can someone help with a workaround?

Thanks

Saviola

You can replace the double quote with single and should be work :

$sql = sprintf(
   "SELECT * FROM $tbl_name  WHERE title LIKE '%s' OR description LIKE '%s' LIMIT $start, $limit",
   [B]mysql_real_escape_string('%[COLOR="Red"]'[/COLOR].$search_term.[COLOR="Red"]'[/COLOR]%'),[/B]
  [B] mysql_real_escape_string('%[COLOR="Red"]'[/COLOR].$search_term.[COLOR="Red"]'[/COLOR]%')[/B]
);

quark76

Works like a charm! Thanks!

quark76

I've got another that I've racked my brain against & tried every way:

$sql = "SELECT username FROM members WHERE `id` = '".$userid."' LIMIT 1";

I'm trying to change it to:

	$sql = sprintf(
	   "SELECT username FROM members WHERE `id` = %d LIMIT 1",
	   mysql_real_escape_string(".$userid.")
	);

But this puts the dots on the value & I've tried a lot of other combinations. Can someone help with this?

bradgrafelman

I think you might want to stop and find some basic PHP tutorials (or read the manual) on how to deal with a [man]string[/man].

The periods in the code above are concatenation operators; they are used to join separate string pieces into one.

Since the value you want to pass to [man]mysql_real_escape_string/man is simply the value of a variable, there's no need to build a string at all - just pass the variable by itself.

Also note that using that function is unnecessary anyway, since the sprintf() function will explicitly cast the value of the variable to a signed integer (hence the '%d').

quark76

Sorry I didn't show it with a string, I have multiple queries with strings of user input. I've searched on the web & cannot find the answer to my question.

I've even dropped the mysql_real_escape_string & still cannot get it to work.

$sql = sprintf(
       "SELECT * FROM users WHERE `uname` = %s LIMIT 1",
       ".$uname."
    );

Thanks for any help.

bradgrafelman

Two things:

In the SQL query itself, the string value for the 'uname' column isn't surrounded in quotes.
You still have unnecessary periods. This:
```
".$uname."
```
will result in data such as:
```
.quark76.
```
because you're telling PHP to insert periods before and after the variable $uname.

Saviola

I think you have to follow advices of bradgrafelman in his previous post.
Why you add double quote around ".$uname." ?
If you keep a lot to solve the problem just do:

 $sql = sprintf(
       "SELECT username FROM members WHERE `id` = %d LIMIT 1",
       mysql_real_escape_string([COLOR="Red"]"[/COLOR].$userid.[COLOR="Red"]"[/COLOR])
    );

have to be :

 $sql = sprintf(
       "SELECT username FROM members WHERE `id` = %d LIMIT 1",
       mysql_real_escape_string($userid)
    );

And :

$sql = sprintf(
       "SELECT * FROM users WHERE `uname` = %s LIMIT 1",
      [COLOR="Red"] "[/COLOR].$uname.[COLOR="Red"]"[/COLOR]
    )

have to be :

$sql = sprintf("SELECT * FROM users WHERE `uname` = %s LIMIT 1", $uname);