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
MySQL INSERT help please!
Moderator: General Moderators
Re: MySQL INSERT help please!
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.
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!
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.
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.
-
davelincoln
- Forum Newbie
- Posts: 2
- Joined: Fri Apr 23, 2010 5:57 am
Re: MySQL INSERT help please!
Thanks so much for your responses, really helped me out there
- Jonah Bron
- DevNet Master
- Posts: 2764
- Joined: Thu Mar 15, 2007 6:28 pm
- Location: Redding, California
Re: MySQL INSERT help please!
Just a side note, the code you posted is SQL (small query language), not PHP.
Re: MySQL INSERT help please!
<pedant>Jonah Bron wrote:Just a side note, the code you posted is SQL (small query language), not PHP.
SQL is structured query language
</pedant>
Cheers,
Dave.
- Jonah Bron
- DevNet Master
- Posts: 2764
- Joined: Thu Mar 15, 2007 6:28 pm
- Location: Redding, California
Re: MySQL INSERT help please!
All-righty-then. Whoever wrote what I read was obviously mis-informed. What's really funny, is that I meant to write "simple".

Re: MySQL INSERT help please!
Ah! You must be thinking of SSSQL the Small Simple Structured Query Language 