[Resolved] Problems with apostrophes in MySQL/PHP

[tickled_pink]

I'm currently going through my PHP code after coming across problems when the user types in apostrophes in a form. The problems that this has been causing seems to be erratic.

For example, I have the following SQL string in one of my PHP scripts:

   $sql = "INSERT INTO pending (business_name,address,district,town,county,postcode,
           telephone,fax,email,contact,date)
     VALUES ('$businessName','$address','$district','$town','$county','$postcode',
       '$telephone','$fax','$email','$contactName','$today')";

   $result = mysql_query($sql) or die ("cannot access pending database");

This works okay with apostrophes. The string even stores any apostrophes properly in the table (no need for an escape character)

However, later on (in a later script - all this is part of a wizard) the script needs to transfer the information from the table accessed above to another table.

 //Copy pending applications to client database
 $sql = "SELECT * FROM pending WHERE app_id = $app_id";
 $result = mysql_query($sql) or die ("Cannot access pending table");
 $row = mysql_fetch_array($result,MYSQL_BOTH);
 $business_name = $row["business_name"];
 $address = $row["address"];
 $district = $row["district"];
 $town = $row["town"];
 $county = $row["county"];
 $postcode = $row["postcode"];
 $telephone = $row["telephone"];
 $fax = $row["fax"];
 $email = $row["email"];
 $contact = $row["contact"];


 $sql = "INSERT INTO clients (business_name, address, district, town, county, postcode,
         telephone, fax, email, contact)
      VALUES  ('$business_name','$address','$district','$town','$county','$postcode',
         '$telephone','$fax','$email','$contact')";
 $result = mysql_query($sql) or die ("Cannot copy client data");

This time, the script exits with the error on the final line above. Anyone have any idea why, when an apostrophe is typed on the form, the first script example works but the second doesn't - even though they are both to contain the same text and the fields being accessed are identical in both.

[mrhappiness]

maybe get_amgic_qutes_gpc is enabled and therefore a ' in your form get's automatically escaped to \' ?

either you take a look at mysql_escape_string() or you try

INSERT INTO table1 (columns)
SELECT (columns) FROM table2 WHERE your_condition

[tickled_pink]

Well, whatever the reason for my scripts sometimes working and sometimes not, it's all been fixed by using addslashes() and stripslashes().

😕

[richie]

It's not a good idea to just addslashes without verifying if get_magic_quotes_gpc() is on. Otherwise you may have multiple slashes. It gets worse when you edit such text. More and more slashes get added each time, to end up with something like:

You just don\\\\\\\\\\\\\\\\'t do that.

The best way to handle this is:

if (!get_magic_quotes_gpc()) {
$lastname = addslashes($POST['lastname']);
} else {
$lastname = $POST['lastname'];
}