如何在MySQL的表单字段中接受撇号

Question

I have a form that accepts a few names and phone numbers and posts them to a PHP script which then inserts those values into a MySQL table. The code (PHP) is pretty straightforward as you can see here:

$sql = "INSERT INTO Contact_table (PHONE, NAME) VALUES ";
for ($i = 0; $i < $arr; ++$i){
    $sql .= "('".$numbers[$i]."', '".$names[$i]."'),";
}
$sql = substr($sql, 0, -1)." ON DUPLICATE KEY UPDATE name = COALESCE(VALUES(name), name);";
$connect = dbconn(PROJHOST,PROJDB,PROJDBUSER,PROJDBPWD);

$query = $connect->query($sql);
$connect = NULL;

And this works perfect except that it refuses to accept any names like O'Hara or D'Souza. I am vaguely aware of PDO's prepared statement having some use in this scenario but I couldn't make it work. What I tried was this:

$query = $connect->prepare($sql);
$query->execute();

Instead of this:

$query = $connect->query($sql);

Any hints? What am I missing?

Answer 1

There are several ways to handle your problem. You can escape your strings using real_escape_string however there, as you suggest better ways. The best one is through prepared statements using either mysqli or PDO.

Since you mention PDO, which is an excellent approach, here's how to handle it this way:

The first thing involves running the connection through try and catch and then you run your query with placeholders, and then bind your variables to these placeholders. Here's a simple way of how it works:

try {
$connect = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
 $stmt = $conn->prepare("INSERT INTO Contact_table (PHONE, NAME) VALUES (:PHONE, :NAME)");
 $stmt->bindParam(':PHONE', $PHONE);
 $stmt->bindParam(':NAME', $NAME);
//then name your variables like so
$PHONE = [whatever your phone number variable(s) is/are equal to];
$NAME = [whatever the NAME variable(s) is/are equal to];
$stmt->execute();
//note the last section can occur within a loop, much like you have above.
catch(PDOException $error)
}
{
echo "Error: " . $error->getMessage();
}
$connect = null;
}

Note, not only will this allow any fields with special characters such as apostrophes, it will run much faster for large queries, and have the added bonus of being a lot more secure, as it pretty much eliminates most methods of SQL injection.

You can also accomplish this with mysqli prepared statements, however PDO has the added advantage of being database agnostic, in that there are easy connections to databases other than MySQL.

Also, you can use the ? method for loosely defined parameters as mentioned in one of the comments, but this also lends itself to being able manipulate these as objects.

Here's a good reference if you wish to learn more: PHP Prepared Statements