Standard text insert error

Chris_Evans

Hi there,
When somebody adds news to the database it appears we have a problem in that when special characters are used:

':@'"}{ etc etc it takes it as MySQL coding, or at least that's what it appears to be doing.

Here's the error the latest post somebody made got:

You have an error in your SQL syntax near 'd say that the new site is nearly complete now, the sections code is done along ' at line 1

So presumably this is something to do with the layout of the database or the way I've coded it.

The database for "body" is layed out as 'text, full text, not null'.

And the query is as follows:

$sql = mysql_query("INSERT INTO news (title,body,date,postby) values ('" . $newstitle . "','" . $newsbody . "',now(),'" . $username . "')")
	or die(mysql_error());

Any ideas?

If you wondered, I used the '" . $ . "' because I wondered whether that prevented this but it doesn't so if anybody would be kind enough to explain what they're for I'd be grateful.

Thanks,

Chris

Pig

seperating them out with . will do nothing for you. You should always validate your forms to make sure crap like <script> or worse aren't put in your dbase.

Create a function and run all your form data through it. check out html special entities and strip tag functions on php.net

Weedpacket

Your error message is pointing out that the SQL command you're sending MySQL is choking on the apostrophe in the supplied text - not surprising really, since that's the character MySQL uses to mark the beginning and end of a string. So it sees the ', figures that's the end of the string, and then finds all this garbage after it.

[man]mysql_escape_string[/man] will fix that, but as Pig notes, you'll have to consider where the text might get used and what effect it will have there; you've already seen what can happen if adequate safeguards aren't in place when putting text into a database (except it can be a lot worse than you've seen).

Chris_Evans

Hi there, thanks for the help and the link! 😃 They came in quite helpful so I'm working my way around all the files and then I'll test them.

That's the wrong way round. 😃 Thanks.

Chris_Evans

I've been looking at preg_replace too, since I've realised that the new paragraphs, eg.

Don't come out at all, everything is within the same paragraph unless you use <br> or <p>. Do you know how I could convert what a blank line is named as? A space is:

:space: according to php.net so just wondered?

Thanks,

Chris

diego25

A new line is "\n", so a blank line could be \n\n. But beware that Windows might make a new line with \r\n and a blank line with \r\n\r\n

Diego

Chris_Evans

Hi there - thanks for that - the info has helped a lot!

Chris

Chris_Evans

Just thought I'd make a quick question to finish off this problem!

I've done the following, the idea being that one a user does this:

Line 1
Blank Line
New paragraph line 2

It creates a <br> in between. I wrote this but it seems to make each letter seperate where I have created a space with a Hi'Hi'Hi.

$subject = preg_replace("'([\r\n])[\s]+'","''", $subject);
$main = preg_replace("'([\r\n])[\s]+'","'<br>'", $main);

It's probably something nice and simple but I can't spot it.

Thanks, Chris.

diego25

You don't need the there...

Also, check out nl2br

Diego

Chris_Evans

Hi - thanks for that!