How do I deal with an apostrophe in my MySQL database that is throwing off my queries?

Question

I have an SQL file I created from a CSV file. It includes the names of all metropolitan areas in the United States, along with some statistics about their populations. I wrote a PHP script that takes the population data, turns it into a chart, and sticks it back in the city's row, in a different column. This script ran fine until it came to Coeur d'Alene, Idaho. I assume it's because of the apostrophe (single quote) in the city's name. Here's the code:

$query = "UPDATE population SET `$columnname`='$chart_url' WHERE name = '$cityname'";
            mysql_query($query) or die;

So it's finding the row by matching it up with the city's name. There isn't any other way to do this, because of the data. Is there a way to deal with data that already has unescaped characters in the SQL file?

Answer 1

$escapedCityname = mysql_real_escape_string($cityname);

Escape your data! And if you can, use binding.

Answer 2

Your data can have apostrophes - but you need to escape in your code:

// You should not use url values directly! The can be hijacked...
// You should write a safe solution here
$column_name = $chart_url;

// Replace cityname with escaped city name
$cityname = mysql_real_escape_string($cityname)
$query = "UPDATE population SET `$columnname`='$column_name' WHERE name = '$cityname'";
            mysql_query($query) or die;

Why do you use a dynamic columnname: $columnname?

Answer 3

Use PDO with named parameters. It makes your code more maintainable, secure, faster and escapes your data by avoiding the dangers of string concatenation.

mysql_* functions are antiquated. If you must use those functions, then espace your data with mysql_real_escape_string.