Copying information in a MySQL table with an apostrophe

[Glenn]

I have a web site set up where I allow users to share(copy) information with each other. If one user has something stored that has an apostrophe, it messes up the copying process. How can this be fixed?

 

[DesignerBill]

Escape the apostrophes before storing the data by using mysql_real_escape_string.

 

[Glenn]

The php code looks like this.


$results = mysql_query("SELECT * FROM questions WHERE user = '$shareduserName' AND subject = '$subject' AND topic = '$topic'");
mysql_query("INSERT INTO topics (userid, subject, topic) VALUES ('$user', '$mysubject', '$mytopic')");

while ($row = mysql_fetch_array($results)) {
$question = $row['question'];
$answer = $row['answer'];


mysql_query("INSERT INTO questions (user, subject, topic, question, answer, ordernum) VALUES ('$user', '$mysubject', '$mytopic', '$question', '$answer', $count)");

$count++;
}

 

[DesignerBill]

I see. You just need to make sure to use mysql_real_escape_string to convert all data that could contain apostrophe before storing it anywhere in the table. One way is like this:

$user = mysql_real_escape_string ($user, connection);
$mysubject = mysql_real_escape_string ($mysubject, connection);
$mytopic = mysql_real_escape_string ($mytopic, connection);​

This function and others are also used to prevent an attack called "SQL Injection". See http://php.net/manual/en/function.mysql-real-escape-string.php.

 

[Glenn]

So, if I escape it before saving it into the database, then remove the \ when a user retrieves the information, how does this solve anything?

 

[DesignerBill]

MySQL removes the \ automatically on retrieval of the data so you can process it normally. If you then rewrite the same data, or copy it as you are doing, you need to escape the string again.

I think I'm giving Glenn correct information, but if not does someone else want to weigh in and help us?