Best way to insert mysql data that has " ' , in it?

[brandon0401]

hey guys, been having a problem and cant quite get it right, can anyone give me any sample code or insight on best way to insert data into mysql from with that has ' " and , in it? I keep getting errors, tried with
' '
" "
, \"" . $title . "\",

, \"" . mysql_real_escape_string($description) . "\",

are main problems

such as:

$sql = "INSERT INTO cache.frontpage (id, section, title, url, image, text, channels, category, tags, views) VALUES (NULL, \"2\", \"" . $title . "\",\"" . $url . "\", \"".$thumbnailUrl."\", \"" . mysql_real_escape_string($description) . "\", \"".channel[0]."\", \"".category[0]."\", \"".$html."\", \"".viewCount[0]."\");";

thanks...

[mrscruff]

How about using, htmlspecialchars($string, ENT_QUOTES), Then ' will become '

And htmlspecialchars_decode($string, ENT_QUOTES); if you need to retrieve it later.

[henry0]

It seems that you are giving yourself a hard time
simply make use of:

$my_var=' "aaaa" ';
remember that double quote encompassed by single quotes won't generate a php error
and the reverse option is true too.

$my_var = mysql_real_escape_string($my_var);
that should do it.

echo my_var shows:
\"aaaa\"

PS) you need a live conn to your DB in order to use
mysql_real_escape_string

[brandon0401]

I am simply connecting to db, and I am using mysql_real_escape_string with no luck..

[henry0]

what is cache.frontpage

[brandon0401]

cache.frontpage is just the db name...

here is example of error, and I am using the \" form as above

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'There have been some big games recently and it is a final push for me. "I have ' at line 1INSERT INTO yidio_cache.frontpage (id, section, title, url, image, text, channels, category, tags, views) VALUES (NULL, "4", "Chelsea v Newcastle","http://www.domain.com", "http://www.domain.com/_Large.jpg", "Chelsea are at home to Newcastle where they will look to keep the pressure on the top of the table. A win at St James' Park would see the Blues equal on points with leaders Manchester United. Joe Cole is determined to make the final Sunday of the Premier League season a real 'squeaky bum' time for Sir Alex Ferguson. The phrase was once famously used by the United boss to describe the title run-in and Cole believes that victory over Newcastle will take the fight for the silverware right down to the wire. The Chelsea and England midfielder is also determined to get back to his best, saying: "There have been some big games recently and it is a final push for me. "I have played a lot of football and my last few games I haven't been at my best - but I want to come back to my best in this game and get the points." He continued: "We had a bad result up at Newcastle last season. We lost Michael Ballack injured as well so it wasn't a good day. "This year we will keep going and we want to take it to the last day. "If we can win and take it to the last Sunday then it is squeaky bum time as the famous man once said." Chelsea have taken more points on their travels this season than either Arsenal or United. They have amassed a total of 39 nine points on the road compared with Arsenal's 33 and United's 32.", "ITN", "Sports", "ITN, sports, football, cricket, soccer", "12156");

[henry0]

did you think (to insert) when using mysql_real_escape_string removing the dots and slashes
further in your php.ini how are set:
safe mode, register global and magic quotes

[brandon0401]

safe_mode = off;

register_globals = off;

; Magic quotes
;

; Magic quotes for incoming GET/POST/Cookie data.
magic_quotes_gpc = On

; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(), etc.
magic_quotes_runtime = Off

; Use Sybase-style magic quotes (escape ' with '' instead of \').
magic_quotes_sybase = Off

And when you say insert when using mysql_real_escape, does that mean just removing my slashes? I am confused..thanks

I setup the query, then execute like this right now
mysql_query($sql);

[henry0]

turn off magic quotes
do not forget to restart Apache
and yes rem the slashes
you do not need that
look at my first example
mysql_real_escape_string does it for you

[henry0]

For now forget about concataination
you should even do it one at a time for test purpose
something like:
<<<<<
$title=mysql_real_escape_string($title);
$url=mysql_real_escape_string($url);
// and all other variables....

$sql = "INSERT INTO cache.frontpage
(id, section, title, url, image, text, channels, category, tags, views)
VALUES (NULL, '2', '$title', '$url', '$thumbnailUrl', ect.....

>>>>>

[brandon0401]

ahh so you have to do the mysql_real_escape_string before putting it into the insert string?

[chorny]

brandon0401, use mysqli or PDO with placeholders

[jatar_k]

>> do the mysql_real_escape_string before putting it into the insert string?

exactly

[brandon0401]

thanks all...got my insert fixed...

is having magic quotes on like I do in php.ini causing this:
when I search "name's whatever"

it will always print as name\'s whatever

thanks.

[brandon0401]

yup, that fixes that problem wahoo! :)

[eelixduppy]

>>is having magic quotes on like I do in php.ini causing this?

Yes, that is where it is coming from because you are escaping it twice now; once with mysql_real_escape_string and the other with magic_quotes. Magic_quotes, however, doesn't do the same job that mysql_real_escape_string does so it is recommended to disable magic_quotes. If you can't, then you can stripslashes [php.net] from the strings and then apply mysql_real_escape_string.

[eelixduppy]

I'm a little slow on the typing today, I guess. Glad you got it resolved :)