MySQL INSERT help please!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
davelincoln
Forum Newbie
Posts: 2
Joined: Fri Apr 23, 2010 5:57 am

MySQL INSERT help please!

Post 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
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: MySQL INSERT help please!

Post 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.
User avatar
Zyxist
Forum Contributor
Posts: 104
Joined: Sun Jan 14, 2007 10:44 am
Location: Cracow, Poland

Re: MySQL INSERT help please!

Post 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.
davelincoln
Forum Newbie
Posts: 2
Joined: Fri Apr 23, 2010 5:57 am

Re: MySQL INSERT help please!

Post by davelincoln »

Thanks so much for your responses, really helped me out there
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: MySQL INSERT help please!

Post by Jonah Bron »

Just a side note, the code you posted is SQL (small query language), not PHP.
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: MySQL INSERT help please!

Post 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.
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: MySQL INSERT help please!

Post 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:
davex
Forum Contributor
Posts: 101
Joined: Sat Feb 27, 2010 4:10 pm
Location: Namibia

Re: MySQL INSERT help please!

Post by davex »

Ah! You must be thinking of SSSQL the Small Simple Structured Query Language :D
Post Reply