撇号和反斜杠悖论

Question

I've been puzzling with this for a while now, and have finally succumb to asking for help here.

I have a preg_match script for scraping, that matches different variables in a loop and stores them in an array, to finally be posted into a SQL table. The problem occurs whenever a variable contains an apostrophe.

Here's how I grab and organize the data

for($i = 0; $i < count($bokse[0]); $i++){
    preg_match_all("/title=\"Mere information om (.+?)\"/sim", $bokse[0][$i], $name, PREG_SET_ORDER);
    $laeger[$i]['navn'] = stripslashes(mysql_real_escape_string($name[0][1]));
}

Now, the value of something that I grabbed, mysql_escaped and stripped can be:

**Michael D'Angelo** [NOTICE THE APOSTROPHE]

If I want to echo this to the page, I can succesfully echo D'angelo correctly (with apostrophe)

echo $laeger[$i]['navn'];

Will give me Michael D'Angelo.

But when it comes to storing this in a database, I run into a paradox; I can chose to either store it as Michael D\'Angelo

Or store the D, and receive an SQL error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'angelo'
(..........blablabv...............)

Here's how I store the data

mysql_query("INSERT INTO table (name) VALUES ('".$laeger[$i]['navn']."'")");  or die(mysql_error());

Depending on if I stripslashes or not, I'll either mess up the query or mess up the result (with a bachflash). FML.

Answer 1

As you noted in your comment, the accepted answer does nothing for you. It will only work if you know in advance what data you are going to be scraping so you can put the backslashes in the correct place, but of course you do not know in advance so you need a solution that will work at run time. MySQL provides such a solution for you.

The answer that solves your issue is to change your insert to:

mysql_query("INSERT INTO table (name) VALUES ('".mysql_real_escape_string($laeger[$i]['navn'])."'")");

Or as I prefer to do it, even though it is non-standard:

 mysql_query("INSERT INTO table set name='".mysql_real_escape_string($laeger[$i]['navn'])."'");

I think that it is much easier to code and much easier to read.

For my own work, I created a "helper function" named "dbstring" that wraps the apostrophes around the escape function which makes everything even more compact, more readable, transportable between databases and SQL injection attack resistant.