MySQL query containing ', possible?

Anon

Hi,
This is my first post here.. Hope someone can help me with this.

I am working on a mp3 database/jukebox. I have made a php script that I am going to use to update the database with all my mp3s.

Problem is that some of the song titles and/or artist contain this letter '. Like this: "i ain't goin' out like that.mp3". When I query the DB with this song title/artist;

$title = "i ain't goin' out like that.mp3"
$artist = "cypress hill"
mysql_query("INSERT INTO mp3 (title, artist) VALUES ('$title', '$artist')");

This query naturally fails with this message: "You have an error in your SQL syntax near 't goin' out like that.mp3, 'cypress hill'"

Do I have to change all the ' to some other letter, or is there a way to do this query some other way?

Grateful for all help,
Kurt

Anon

you need to do this when entering the data into the database
string addslashes (string str)

then this when bringing the data back our
string stripslashes (string str)

Anon

I saw something once that PHP4 automatically does slash work (you previously could by choice), and that using AddSlashes could possibly double-up the slash'n. Any notes/know what the situation is?

Anon

PHP will automatically add slashes to text form elements submitted if magic_quotes is on in the php.ini file.

Kurt, what addslashes() does is escape the single quote that's causing your problem. The single quote is special in mysql syntax, so if you want a literal single quote, you must proceed it with an \ character. addslashes($string) does this for all single and double quotes in $string.

Also, pcordero:

You don't need to stripslashes when you pull the information out of the database. If you do something like

insert into table values ('It\'s A Test');

and you look at the data, the escape character isn't saved. It just allows you to input the data, but it's not saved. See what I mean...

Hope that helps everyone.

---John Holmes...

Anon

This may sound dense, but where do I put this command when I'm inserting into the databse?

Anon

Okay...assuming you have a string containing the file name, something like

$song = "Ain't it beautiful.mp3";
$song = addslashes($song);
$result = mysql_query("INSERT INTO table (song) VALUES ('$song');

Does that clear it up?

---John Holmes...

Anon

It was the other girl that needed help with the songs 🙂 My form has a lot of variables... Would I have to write that out for every one? My reads insert statement SQLRequest= "INSERT INTO .... etc

I tried turning on magic quotes by editing the value to on in php.ini but do I have to stop and restart ISM or anything before it takes effect? Because thus far, it isn't working.

Please bear with me, I've been writing PHP for a total of four days 🙂 Thanks for your help.

Anon

No, when I try it your way, the database won't connect at all.

Anon

$db = odbc_connect("amf.mdb","username","password");
$SQLRequest = "INSERT INTO forumguests (session, RC, WS, UNIT, surname,
firstname, office, title,
section, phone, fax, mailaddress, foodAllergy, foodAllDetail,
specialDiet,
specialDietDetail,
simulTrans, simulTransDetail, otherReq, otherReqDetail, arrival, depart,
smoking, labourrelations,
coaching, communicating, leadmanagement, balancing) VALUES
('$session','$RC','$WS','$UNIT','$surname',
'$firstname','$office','$title','$section','$phone','$fax','$mailaddress','$
foodAllergy',
'$foodAllDetail','$specialDiet','$specialDietDetail','$simulTrans','$simulTr
ansDetail',
'$otherReq','$otherReqDetail','$arrival','$depart','$smoking',
'$labourrelations',
'$coaching', '$communicating', '$leadmanagement', '$balancing')";
odbc_exec($db, $SQLRequest);

Anon

Magic_quotes would be your solution if you have that many variable, otherwise, yes, you would have to run stripslashes on every one of them.

Check the line in your php.ini file,

magic_quotes_gpc = On

It's on by default. Then run a page with phpinfo() and check that file to make sure the change was made. You'll have to restart your webserver, I believe.

---John Holmes...

Anon

Would just like to thank everyone.. My querys now works like I want! 🙂

Kurt