ansaurus

Question

Answer 1

+2 A:

This error message means that your single quotes aren't being escaped properly. So someone has entered Saint David's Park Baptist Church or something like that and it has ended up being sent to MySQL as is. I suspect that some server setting such as magic quotes is different between the 2 servers.

I also suspect that your code may be vulnerable to SQL injection if you are just relying on magic quotes but don't know enough about PHP to know what the best practices are. Edit: I think Flakron's answer covers that.

Martin Smith 2010-07-27 11:31:20

got it, thanks for your time :)

Barry McGee 2010-07-27 11:57:07

Answer 2

+2 A:

don't forget to escape input, this way you protect your database from sql injects and this error that you are having e.g

$intro = mysql_real_escape_string($_POST['intro']);

Flakron Bytyqi 2010-07-27 11:35:14

This fixed it, Many thanks :)

Barry McGee 2010-07-27 11:56:50

Answer 3

+3 A:

right syntax to use near 's Park Baptist Church

Is a dead giveaway that you have an SQL injection problem. Apart from making it impossible to use values with apostrophes in, this can also be a serious security issue, if untrusted user input ever makes it into one of those strings.

$query = "SELECT * FROM regions WHERE region = '".$_SESSION['region']."'";

Yeah, that's super-broken. You must use mysql_real_escape_string() on any text content you are attempting to insert into an SQL string literal. Every single one of your queries appears to be vulnerable.

You can write a shorthand function to save typing:

function m($s) {
    return "'".mysql_real_escape_string($s)."'";
}

$query= 'SELECT * FROM regions WHERE region='.m($_SESSION['region']);

In the longer term, consider using parameterised queries (mysqli or PDO), which avoid string-escaping issues.

This should never have worked before, but might have been hidden by a magic_quotes_gpc deployment option on the old server. However this option is acknowledged by the PHP devs as a horrible mistake. Whilst you could try turning it on as a temporary measure until you have audited all the SQL in the application (or until it is removed from PHP, whichever comes first), it will typically introduce further errors, and often fail to actually secure the application.

Also everywhere you put an arbitrary string into HTML, you need to be calling htmlspecialchars(). Otherwise, any < or & characters in the text are going to cause problems, and again if user input is involved you have a cross-site-scripting security mess. Just flicking through, this definitely has user input:

echo "<form action='".$_SERVER['PHP_SELF']."' method='POST'>";

this is a very common HTML-injection hole; the user can put an apostrophe in the URL, causing PHP_SELF to break out of the action attribute and allow an attacker to inject script content into the document.

Also, just on a quick flick through, you've got unescaped & characters in href attributes (&sect in particular can cause you problems as there is an HTML entity with that name), and the interposition of active PHP code and output HTML makes the application desperately unmaintainable.

In short, the application you have been handed is a horror of the worst kind, exhibiting pretty much every anti-pattern excreted by naïve PHP beginners. Consider re-writing, and further consider applying physical violence to the perpetrator.

bobince 2010-07-27 11:53:34

Just read up on injection attacks, many thanks for the comprehensive heads up

Barry McGee 2010-07-27 12:01:47

ansaurus

tags:

views:

answers:

Incorrect mySQL syntax error

related questions