• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

MySQL, quote_smart, and LIKE

I have a database app that needs to check if a first name/last name combination already exists in the database, and if a first name/last name combination LIKE the submitted names exists.

I have the following working:
-------------------------------------------------------------------------------
// check that the submitted first name and last name do not exist in the database
$sql_name_check = sprintf("SELECT first_name, last_name FROM tm_users WHERE first_name=%s AND last_name=%s",
      quote_smart($first_name),
      quote_smart($last_name));
$sql_name_check=mysql_query($sql_name_check);

Do some stuff
-------------------------------------------------------------------------------

but the following does NOT return a LIKE match
-------------------------------------------------------------------------------
// check that the submitted first name and last name are not LIKE an existing name in the database
$sql_like_name_check = sprintf("SELECT first_name, last_name FROM tm_users WHERE first_name LIKE '%%s%' AND last_name LIKE '%%s%'",
      quote_smart($first_name),
      quote_smart($last_name));
$sql_like_name_check=mysql_query($sql_like_name_check);

Do some other stuff
-------------------------------------------------------------------------------

What is the proper query using quote_smart() to run the LIKE query?

Thanks.

Alan

// quote_smart (quote variable to make safe)
function quote_smart($value) {
        // Stripslashes
      if (get_magic_quotes_gpc()) {
            $value = stripslashes($value);
            }
      // Quote if not a number or a numeric string
      if (!is_numeric($value)) {
            $value = "'" . mysql_real_escape_string($value) . "'";
            }
      return $value;
      }

0
alanpollenz
Asked:
alanpollenz
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue, I guess, is here the %% within the sprintf... as that means a litteral % character...
you will need %%%s%% instead of %%s%...

let's see if this goes better:

$sql_like_name_check = sprintf("SELECT first_name, last_name FROM tm_users WHERE first_name LIKE '%%%s%%' AND last_name LIKE '%%%s%%'",
      quote_smart($first_name),
      quote_smart($last_name));
$sql_like_name_check=mysql_query($sql_like_name_check);
0
 
Michael701Commented:
you don't need the sprintf stuff, just use string function

$sql_name_check = "SELECT first_name, last_name FROM tm_users WHERE first_name=".quote_smart($first_name)." AND last_name="quote_smart($last_name);
$sql_name_check=mysql_query($sql_name_check);

and

$sql_like_name_check = sprintf("SELECT first_name, last_name FROM tm_users WHERE first_name LIKE '%". quote_smart($first_name)."%' AND last_name LIKE '%".quote_smart($last_name)."%'",
0
 
alanpollenzAuthor Commented:
Thanks guys.  Unfortunately, neither one of the above worked; however, you did get me thinking in a related direction, so I'll split the points.

Here's what I got to work:

$sql_like_name_check = sprintf("SELECT first_name, last_name FROM tm_users WHERE first_name LIKE %s AND last_name LIKE %s",
      quote_smart("%$first_name%"),
      quote_smart("%$last_name%"));
$sql_like_name_check=mysql_query($sql_like_name_check);

Note the literal inside the quote_smart call.

Thanks again.

Alan
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, overlooked that :-)
glad we could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now