6 replies to this topic

[marklcfc]

I have came across an error on my website, I wondered if anyone could help? I have the word Banks O'Dee in my php database ($buyfrom) and I receive this error when accessing my page..

Quote

select location from opposition_team where opposition='Banks O'Dee'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 'Dee'' at line 1

And this is the code I use on my php page for this part

$q4="select location from opposition_team where opposition='$buyfrom'";
$qr4=mysql_query($q4,$ccppdbc)or die($q4.mysql_error());
$r4=mysql_fetch_object($qr4);
$location=$r4->location;

Can I not make this work without changing the word Banks O'Dee so it doesn't have the ' as I believe that is whats causing it.

[firey]

php has a function addslashes you could do something like:

$q4="select location from opposition_team where opposition='" . addslashes($buyfrom) . "'";
$qr4=mysql_query($q4,$ccppdbc)or die($q4.mysql_error());
$r4=mysql_fetch_object($qr4);
$location=$r4->location;

[AnthonySterling]

You need to properly escape the value in the $buyfrom variable.

$buyfrom = mysql_escape_string($buyfrom);
/* code detailed above follows */

[Aergan]

You should always escape or validate variables before they are used in any form of query - leads to many issues and it's a great big security hole.

[marklcfc]

I used that escape line, the page loads but it appears as Banks O\'Dee instead of Banks O'Dee

Firey's works well though..

Edited by marklcfc, 28 January 2013 - 21:11.

[ITOps]

marklcfc, on 28 January 2013 - 20:37, said:

I have came across an error on my website, I wondered if anyone could help? I have the word Banks O'Dee in my php database ($buyfrom) and I receive this error when accessing my page..



And this is the code I use on my php page for this part

$q4="select location from opposition_team where opposition='$buyfrom'";
$qr4=mysql_query($q4,$ccppdbc)or die($q4.mysql_error());
$r4=mysql_fetch_object($qr4);
$location=$r4->location;

Can I not make this work without changing the word Banks O'Dee so it doesn't have the ' as I believe that is whats causing it.

Hi marklcfc,

What others have said is true about needing to escape your queries, I would recommend the using mysql_real_escape_string function as mysql_escape_string has been depreciated since php 5.3 in June 30, 2009.

Since this is an issue showing up in your site there is also the possibility that there are other unescaped queries in your website application. I would recommend updating your code to use PDO and prepared statements to help increase the security of your site and help protect from SQL Injection.

I would also recommend validating your data before accepting it from the end user or from a storage system and using something like HTMLPurifier to run your data through to assist with XSS protection.

Please take some time checking out the OWASP Top 10 to get a good idea of some of the security issues to look into. This should help you review your current site security level and see where it needs to be improved.

[palenous]

marklcfc, on 28 January 2013 - 21:07, said:

I used that escape line, the page loads but it appears as Banks O\'Dee instead of Banks O'Dee

Firey's works well though..

To fix this issue, you just need to run stripslashes on your strings before echoing them out:

$buyfrom = mysql_real_escape_string($buyfrom);
$q4="select location from opposition_team where opposition='" . $buyfrom . "'";
$qr4=mysql_query($q4,$ccppdbc)or die($q4.mysql_error());
$r4=mysql_fetch_object($qr4);
$location=stripslashes($r4->location);

The escaping is only necessary when inserting data into your database. Once you retrieve it, you can strip the slashes to return your data to normal. Hope that answers your questions!