PDA

View Full Version : single quotes & double quotes insert into mysql



shyne
07-23-2007, 10:49 PM
Hi

I have a php news like script where I can submit news etc. Now in textarea if I have a news that has "kjlk" double quotes like that then it gives out an error when you submit it to be saved in database. How can I make the double quotes or single quotes or any other character that is illegal to be inserted in mysql db to be saved in the mysql database without any error.

Thanks alot and please do show me with an example. I am lil newbie at all this.

djr33
07-24-2007, 02:07 AM
First, this might just be a PHP error.

Using echo, as an example, here are some problems and solutions:

echo 'It can't happen.';
That is wrong because the single quote ends the string.
You can escape it like this:
echo 'It can\'t happen.';
Alternatively, use the other kind of quotes:
echo "It can't happen.";

Within double quotes, single quotes are fine; within single, double are.

Escaping the same quote as used to surround the string is done with a slash.


Now, for MySQL:
If you were to include a user defined variable, like from a form, directly into a MySQL database, this is a major security risk. If they were to use as the value: "something; DELETE DATABASE `mydb`;", then this would end the current command and start another, giving that user full control.

This is an important thing to know, if somewhat unrelated to your current problem.

The way to solve both, though, is the same. Using mysql_real_escape_string(), you can make this safe, converting characters to escaped data, so it will be inserted but not executed.
Before using anything in the query, you can change it like so:
$variable = mysql_real_escape_string($variable);

You could do this to a portion of the query itself, though it would likely cause trouble for anything that would need to use command characters, like quotes, etc.

http://php-mysql-tutorial.com is a good site to learn.

acholinet.com
11-25-2007, 05:29 PM
I laboured attempting to insert data from one mysql database to the other...after executing numerous php- mysql codes, i kept running into the problem associated with "double qoute" errors. I then used the stripslash() addslash() html entitities("" enqoute), str_replace() etc. But all in vain the above solutions could only solve one or the other...the errors where either from "html based content" or "double quoted text content", and in other cases the mysql engine was executing key words such as 'from' 'and' 'as' from my data content instead of storing the data.

After almost six hours of head aching and numerous attempts, a search with "php mysql insert into double qoutes problem" on google brought me here to dynamic drive. With just this simple hint; "mysql_real_escape_string()" my job was done in 2 minutes. Thanks "djr33" for this simplistic yet a very million dollar hint.

here is the simple but effective php script that I used on www.acholinet.com. It might help some other amatuer like me save time in copying data from one mysql table to the other without running into these so called 'double quotes' etc problems

<?
//inlcuded code to connect to mysql database

include ("connecttodatabse.php");

//sql query section
//got data from mysql table text_content to insert in mysql table data_content
//notice the detail mysql error catch..after all my fustrations

$count = 0;
$q1 = "select * from text_content";
$r1 = mysql_query($q1)or die("<b>A MySQL error occured</b> Query: " . $q1 . " <br /> Error: (" . mysql_errno() . ") " . mysql_error());

while($a1 = mysql_fetch_array($r1))
{
$count++;
$title = mysql_real_escape_string($a1[title]);
$catid = mysql_real_escape_string($a1[catid]);
$introtext = mysql_real_escape_string($a1[introtext]);
$full_text = mysql_real_escape_string($a1[full_text]);

//inserted data into the columns of the new table called 'display'

$q2 = "insert ignore into display set news_subject = \"$title\",
news_cat = \"$catid\",
news_news = \"$introtext\",
news_extended = \"$full_text\" ";
$f1 = mysql_query($q2) or die("<b>A MySQL error occured at content: $count</b> Query: " . $q2 . " <br /> Error:
(" . mysql_errno() . ") " . mysql_error());


}
echo " Number of records copied is: $count";

djr33
11-25-2007, 09:18 PM
In your mysql statement, you're using double quotes. I don't see the point. Using single quotes should solve it (I think, though I haven't checked everything else), and that's what I've learned to do (though double may be technically valid as well).