mysql real escape string() not working

[mr_nabo]

Hi,

Confused by this as I think it's actually escaping characters based on the error I'm getting when I run it:

ESCAPING CHARACTERS:

// Connect to database etc., then:
// ESCAPE CHARACTERS
$comments = $_POST['comments'];
$comments = mysql_real_escape_string($comments);
// I assume I would do this for all input fields where text or numbers can be filled in such as inputs for an email address and age?

SQL QUERY:

// Insert the data from form
$sql="INSERT INTO tablename (
FieldOne,
FieldTwo,
Comments)

VALUES (
'$_POST[FieldOne]',
'$_POST[FieldTwo]',
$comments)";

// Run mysql_query()

MYSQL ERROR:

Error: 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 '\r\n \r\n\'Single quotes\'\r\n\r\n\"Double\"\r\n\r\n/ Slashes\r\n// Double slash' at line 27

Any ideas why I'm getting that error? Also, is purely using mysql_real_escape_string() all I should be doing before inserting my form submission data into my database?

Just want to make sure I'm doing all I should be to prevent a hack attack.

Thanks

mn

PS, I should have said - these are off:

magic_quotes_gpcOffOff
magic_quotes_runtimeOffOff
magic_quotes_sybaseOffOff

[mr_nabo]

Solved it, apologies - I hadn't added single quotes around the variable in my SQL query:

VALUES (
'$_POST[FieldOne]',
'$_POST[FieldTwo]',
'$comments')";

Coding can be so unforgiving!

However, am I right in thinking that this is all I need to do with the data entered in my form inputs?

Thanks

[Readie]

mysql_real_escape_string will protect you from a SQL injection attack, but if you are echoing the user's input anywhere you are still a bit vulnerable to a HTML style injection attack.

I personally protect against that with a custom function, mine is much bigger than this, but this is *enough* for protection.

function webify($input) {

$symbols = array(
'/&/' => '&',
'/[\\\\]{1}\'/' => ''',
'/[\\\\]{1}"/' => '"',
'/</' => '&#60;',
'/>/' => '&#62;',
'\'' => '&#39;',
'"' => '&#34;'
);

foreach($symbols as $symb => $repl) {
$input = preg_replace($symb, $repl, $input);
}
return $input;
}

[mr_nabo]

Thanks Readie, that's kind of you to share that.

[Matthew1980]

Hi there Mr_nabo,

OR:-

$sql= " INSERT INTO `tablename` (
`FieldOne`,
`FieldTwo`,
`Comments` ) VALUES (
'".$_POST['FieldOne']."',
'".$_POST['FieldTwo']."',
'".$comments."')";

As you were using " double quotes for encasing the query, you would need to concatonate the vars into the string to keep the single quotes in the array reference. ;-p

And this would be adviseable too:-

$comments = mysql_real_escape_string(strip_tags($_POST['comments']));

If you get issues with using strip_tags, use htmlentities() instead..

And for the magic_quotes_gpc(); just do this (I think):-

Check to see if they are on, if not do the decent thing ;-p
if(!magic_quotes_gpc())
{
$comments = addslashes($comments);
}

etc...

Cheers,
MRb