Apostrophes using a textarea [PHP 5]

[NiallAA]

Hi all,

 

This is a bit of a confusing one.  I have been using a script for a while now, very basic, which submits a textarea via a form to a MySQL database using UPDATE.  It's never been a problem on the GoDaddy servers I'm using which run on PHP 5.3 and MySQL 5.5.

 

I am trying to use the same script on a 1&1 internet hosted package, which uses PHP 5.4 and MySQL 5.1.  It throws up MySQL syntax errors when I submit the exact same form. 

 

Before the UPDATE statement, I define a variable using $_POST info and strip the white space and replace the line breaks. 

$report = str_replace("\r\n",'<br>', trim($_POST['report']));

Is this just a case of differing MySQL versions?

 

Niall

Edited November 19, 2015 by NiallAA

[Ch0cu3r]

Can you also post the bit of code which is inserting your data into the database? and also post how you are sanitizing your user input too

 

 

 

Before the UPDATE commend, I create a variable using $_POST info and strip the white space and replace the line breaks.

$report = str_replace("\r\n",'<br>', trim($_POST['report']));

No, you should not be doing that. Newlines should be converted to HTML line break tags when you go to display the content not when storing it. And rather that do the conversion manually use PHP nl2br function instead.

[NiallAA]

Can you also post the bit of code which is inserting your data into the database? and also post how you are sanitizing your user input too

 

No, you should not be doing that. Newlines should be converted to HTML line break tags when you go to display the content not when storing it. And rather that do the conversion manually use PHP nl2br function instead.

 

Thanks.  I have done so 

 

Sanitising (I guess this should also be done using nl2br? ):

$matchdata['report'] = str_replace('<br>',"\r\n", $matchdata['report']);

User input field:

<textarea name="report" cols="45" rows="15"><?php echo $matchdata['report'] ?></textarea>

[Ch0cu3r]

No, nl2br has nothing to do with sanitizing. I just telling you not to manually convert the newlines to HTML line break tags.

 

Sanitizing is where you are escaping user input to make it safe to use within SQL queries. Typically this is done either by using mysqli_real_escape_string or using prepared statements. Are you not doing neither of those? If not then that is most likely the issue. Failure to do so will make your code prone to SQL Injection attacks

 

I suspect the reason your code works on godaddy is because they have a setting enabled called magic quotes, which automatically escapes any quotes in user input being submitted. This was removed as of 5.4 and so your quotes are not being escaped on 1&1 which is leading to syntax error in your SQL query.

 

If you sanitize your user input properly then quotes within user input should not have any affect on your queries.

[NiallAA]

Thank you very much, that's a great help.

 

I shall look into the use of mysql_real_escape_string once again. Whatever I did was not successful the first time. These textareas are to contain basic html, in particular <a href=""> tags in the middle of several paragraphs.  The inverted commas seem to almost always inherit a \ when using mysql_real_escape_string, and add a further \ each time the box is saved.  It'll take a bit of trial and error I suppose, but needs must, as I won't be on PHP 5.3 forever and I shall need to look into moving with the times.

 

Thank you very much for your assistance.