Escaping text

[Jonob]

Hi all,

 

I have a mysql statement as follows:

 

SELECT id, name
FROM customer
WHERE customer_type IN ('J', 'L')

 

The variables in the brackets (i.e J, L, etc) can be passed in through a php function. Note that these variables are strings, and not integers (and have to remain as such).

 

My problem is in trying to escape the input from the php function, and I land up with something like:

 

SELECT id, name
FROM customer
WHERE customer_type IN \'J\', \'L\')

 

Which of course mysql doesnt like.

 

Any ideas on the best way to get around this?

[kickstart]

Hi

 

Use mysql_real_escape_string.

 

All the best

 

Keith

[Mchl]

And use it BEFORE you put variables into query, not after.

[Jonob]

Sorry, I obviously didnt explain myself properly - I AM using mysql_real_escape_string on each variable, and thats what results in the query looking as follows:

SELECT id, name
FROM customer
WHERE customer_type IN (\'J\', \'L\')

 

But mysql doesnt like that query - the escapes cause it to throw an error.

[kickstart]

Hi

 

That suggests you have a variable that contains 'J' (complete with the inverted commas). If so you would want to be looking for '\'J\''

 

All the best

 

Keith

[Mchl]

Can you show us how exactly you create this query?

[Jonob]

Can you show us how exactly you create this query?

Sure, the simplified version is something like:

 

function get_customer($customer_type=null)
{
  $sql = "SELECT id, name
  FROM customer";
  $sql .= ($customer_type != null) ? " WHERE customer_type IN (" . mysql_real_escape_string($customer_type) . ")" : '';
    
  //query database and return result, etc
}

 

The customer_type field in the customer table is a char(1) field. The values are not stored as (for example) 'J', but just as J (i.e. without inverted commas).

 

The query doesnt work if its something like the following:

SELECT id, name
FROM customer
WHERE customer_type IN (J)

 

It does work if its like this:

SELECT id, name
FROM customer
WHERE customer_type IN ('J')

 

I need to escape the input, but as soon as I do that, it fails (as described previously)

 

[kickstart]

Hi

 

Appears all you need is:-

 

function get_customer($customer_type=null)

{

  $sql = "SELECT id, name

  FROM customer";

  $sql .= ($customer_type != null) ? " WHERE customer_type IN ('" . mysql_real_escape_string($customer_type) . "')" : '';

   

  //query database and return result, etc

}

 

You need to escape input so that any characters in the field that are meaningful to SQL are converted to something equivalent that isn't meaningful to MySQL. This isn't really anything to do with surrounding a character value with inverted commas.

 

All the best

 

Keith

[Mchl]

Is $customer_type always a single value, or can it be a comma delimited string? If it's the latter you need to explode it first, escape each value, put each value in single quotes, and implode it again.

 

If it's alway a single value, it should look like this:

 

function get_customer($customer_type=null)
{
  $sql = "SELECT id, name
  FROM customer";
  $sql .= ($customer_type != null) ? " WHERE customer_type = '" . mysql_real_escape_string($customer_type) . "'" : '';
    
  //query database and return result, etc
}

(note additional single quotes)

[Jonob]

Thank you kickstart and Mchl - you have both been very helpful.

 

$customer_type could indeed be a comma-delimited string, so explode, escape, add single quotes, implode worked perfectly.