Apostrophe problems

netfrugal

I'm practicing inserting data into Mysql with PHP on my localhost. As long as I do not include apostrophes, or if I add the backslashes to where I need apostrophes I am fine.

I'd like to add apostrophes at will, without the need for backslashes.

here is my code that inserts the data from my form into the database:

<?

//check for required fields
if ((!$POST[format]) || (!$POST[title])) {
header("Location: /show_addrecord.php");
exit;
}

//set up database and table names
$db_name ="my_music";
$table_name ="music";

//connect to MySQL and select database to use
$connection = @mysql_connect("localhost","root","") or die(mysql_error());

$db = @mysql_select_db($db_name,$connection) or die(mysql_error());

//create SQL statement and issue query
$sql = "INSERT INTO $table_name (format, title, artist_fn, artist_ln, rec_label, my_notes, date_acq) VALUES ('$POST[format]', '$POST[title]', '$POST[artist_fn]', '$POST[artist_ln]', '$POST[rec_label]', '$POST[my_notes]', '$_POST[date_acq]')";

$result = @($sql,$connection)or die(mysql_error());

?>

If I include apostrophes I get this error:
You have an error in your SQL syntax near 's .........

help!

Marc

Plasma

Instead of:

$_POST[format]

Use:

addslashes($_POST['format'])

You must escape your variables before injection (This will escape quotes and other characters).

sajlent

You have to escape all data from form before you can insert them in DB. Also for security reason. Use addslashes or mysql_real_escape_string function. Also you can have a look at http://www.php.net/manual/en/security.magicquotes.php

TobyRT

rather than posting your fields directly, I'd apply some validation first - plus you don't want to be vulnerable to sql-injection attacks.

$format=mysql_real_escape_string($_POST['format']);

will help clean your data of any nasties.

I'mnot sure, but I suspect it may automatically escape your apostrophes too, so you can apocopate to your heart's content.

If that doesn't work (although i reckon you should do it anyway), use the addslashes command eg:

$format=addslashes($_POST['format']);

just make sure that when you call it for display, you use the stripslashes.

I also suspect that if you have get_magic_quotes running on your local machine, that may do it automatically. I recommend you read up on them, as they'll be a lot clearer than me!