Page 1 of 1

MySQL INSERT help please!

Posted: Sun Apr 25, 2010 8:43 am
by davelincoln
Hello

Im sure there is a simple answer to this that im not seeing but anyways....

I am using the php code:
INSERT INTO blog (title,date_day,date_month,date_year,time,auth_id,description,cat_id) VALUES ("'.$title.'","'.$_POST['date_day'].'","'.$_POST['date_month'].'","'.$_POST['date_year'].'","'.$_POST['time'].'",'.
$_POST['author'].',"'.$description.'","'.$category.'")';

To insert a blog entry into my database, which works fine. The problem comes because I am using CKeditor/CKfinder as the $description value.

If someone uses the WYSIWYG editor to upload a photo (for example) this generates typical HTML code for an image, something like <img src="images/logo.jpg" alt="logo" width="409" height="64"/> Which is added to the $description string.

When i try and insert this record, it brings up an error because of the "" marks in the image code is affecting the SQL string.

Kind of annoying because I know why its not working but cant work out how to fix it!

Any help you guys can offer would be greatly appreciated, thanks in advance

Re: MySQL INSERT help please!

Posted: Sun Apr 25, 2010 11:34 am
by davex
Hi,

You need to escape the string with slashes, best bet is to use mysql_real_escape_string which also protects you against SQL injection attacks.

http://www.php.net/manual/en/function.m ... string.php

Cheers,

Dave.

Re: MySQL INSERT help please!

Posted: Sun Apr 25, 2010 11:37 am
by Zyxist
The answers are:

1. addslashes()
2. mysql_real_escape_string() (not perfect is some cases, as it sanitizes also new line symbols).
3. PDO library and data binding.

And anyway, you should escape all your data put into a query that you are not 100% sure that they will not contain a quote symbol. Example:

1. An item title - this is a string, so it may contain a quote. Escape.
2. A number. If you remember about proper validation and checked it with for example ctype_digit(), you can be sure that if they contain any quote, the script won't pass them. Don't escape.

Note: PHP contains a "feature" called "magic quotes" which was supposed to solve this problem by escaping all the data that come to the script. But this is not a solution and can give you more problems than benefits. Especially, the data do not have to go to the database and in this case escaping them against quotes is a nonsense. You should write your scripts as "magic quotes" were turned off, and if they are turned on - rolling back their effect with an extra filter. Magic quotes will be removed in PHP 6 (finally, I think it should be done years ago).

PS. I see that davex already answered you, but I decided to post mine, because there's some extra information that you may find useful.

Re: MySQL INSERT help please!

Posted: Mon Apr 26, 2010 3:01 am
by davelincoln
Thanks so much for your responses, really helped me out there

Re: MySQL INSERT help please!

Posted: Mon Apr 26, 2010 11:33 am
by Jonah Bron
Just a side note, the code you posted is SQL (small query language), not PHP.

Re: MySQL INSERT help please!

Posted: Mon Apr 26, 2010 12:45 pm
by davex
Jonah Bron wrote:Just a side note, the code you posted is SQL (small query language), not PHP.
<pedant>
SQL is structured query language
</pedant>

Cheers,

Dave.

Re: MySQL INSERT help please!

Posted: Mon Apr 26, 2010 2:09 pm
by Jonah Bron
All-righty-then. Whoever wrote what I read was obviously mis-informed. What's really funny, is that I meant to write "simple".

:crazy: :drunk:

Re: MySQL INSERT help please!

Posted: Mon Apr 26, 2010 3:52 pm
by davex
Ah! You must be thinking of SSSQL the Small Simple Structured Query Language :D