[php] MySQL query error if quotes are used

[cromertech]

I'm getting a bit puzzled by this. We have a mentor system so that the teachers can add mentor sessions, concerns and follow up data. This has been sent to us from another school and I have adapted it to our needs.

If they add a concern that contains quotes (' or ") it throws a wobbly and the following error appears

 

Query INSERT INTO tutorial_data (student_id, tutorial_date, teacher_id, details) VALUES ('9990', now(), 'admin', ';'' )
Result 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 '';'' )' at line 2

 

I have a copy of this system on my computer and do not get this error. My guess is it's something to do with the configuration of php/MySQL somewhere but I have no idea where to start.

 

Works fine without quotes so it's getting stuck with not escaping the characters correctly.

 

Can someone point me in the right direction on this as web coding is not a strong point of mine.

[damien.deakes]

You need to use htmlspecialchars to strip special characters out eg...

 

$new = htmlspecialchars("Test", ENT_QUOTES);
echo $new; // Test
?>

 

Hope that helps

[cromertech]

There must be another way as I have two of these systems running on different machines and only get the problem on one.

 

something in php.in to make this a global setting?

[cromertech]

Just in case someone else has this problem the relevant options in php.ini are

 

magic_quotes_gpc = On and

magic_quotes_runtime = On

 

This makes php automatically escape characters that would otherwise cause these errors.

[pcstru]

There is a setting : magic_quotes_gpc, which might be the cause of the difference. It's deprecated as of 5.3.0 though. Use mysql_real_escape_string() in the code to deal with data that may contain quotes.