Topic: [SOLVED] PHP mysql insert problem

[abch624]

Hi Guys,

I have a sql query that is not working when I enter a value with single quotes. The query is listed bellow:

$sql_check = "SELECT * FROM venue WHERE name = '$restaurant_name' AND address = '$address' AND city = '$city' AND country = '$country' AND postcode LIKE '%$postcode%'";

SELECT * FROM venue WHERE name = 'Inn The Park Restaurant' AND address LIKE '%St James's Park, Westminster%' AND city = 'London' AND country = 'United Kingdom' AND postcode LIKE '%W86TA%'

I carry out the following functions on the user input:

$address = $_POST['address'];
$address = filter_var($address, FILTER_SANITIZE_STRIPPED);
$address = mysql_real_escape_string($address);

Any help why I get an error or any other function I need to run on the user input?

Cheers - Zahid

[PFMaBiSmAd]

Any help why I get an error

Only if you post the error so we would have a clue about the point where the problem is being detected.

And the raw query that you posted does not have the same syntax for the address as the populated query, so we are somewhat unsure as to what your code really is.

[abch624]

The query is

SELECT * FROM venue WHERE name = 'Inn The Park Restaurant' AND address = 'St James's Park, Westminster' AND city = 'London' AND country = 'United Kingdom' AND postcode LIKE '%W86TA%'

and

SELECT * FROM venue WHERE name = '$restaurant_name' AND address = '$address' AND city = '$city' AND country = '$country' AND postcode LIKE '%$postcode%'

I get the error of

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Park, Westminster' AND city = 'London' AND country = 'United Kingdom' AND post'"

Do let me know if you need more info.

[PFMaBiSmAd]

You need to use mysql_real_escape_string() on each piece of string data put into the query. Your actual code is not doing that or your actual code is removing the escape characters after they have been added.

[AwptiK]

The apostrophe (') in James's.

That makes address = 'St James'(error here)

[abch624]

The apostrophe (') in James's.

That makes address = 'St James'(error here)

I know that is causing the problem the remedy is what I was looking for...

[JJ2K]

You need to escape the single apostrophe with a backslash like so:

St Jame\'s Park

which makes the query:

SELECT * FROM venue WHERE name = 'Inn The Park Restaurant' AND address = 'St James\'s Park, Westminster' AND city = 'London' AND country = 'United Kingdom' AND postcode LIKE '%W86TA%'

Notice the backslash

[PFMaBiSmAd]

Cannot really help you without seeing the code responsible for the symptoms -

You need to use mysql_real_escape_string() on each piece of string data put into the query. Your actual code is not doing that or your actual code is removing the escape characters after they have been added.

[abch624]

Cannot really help you without seeing the code responsible for the symptoms -

You need to use mysql_real_escape_string() on each piece of string data put into the query. Your actual code is not doing that or your actual code is removing the escape characters after they have been added.

Thanks a lot. I could not copy and paste the full code here due to copyright. But I have solved the problem Cheers

[PFMaBiSmAd]

No one asked you to post the full code, only the code responsible for the symptoms, i.e. the relevant code from the point it is escaping the data up to and including the code putting that data into the query.