为什么单引号会阻止准备好的查询中的参数替换？

Question

I spent some time chasing a bug in my code, and I found the issue. I was using prepared statements and using an array to replace the parameters. My issue was that by enclosing the named parameter in the prepared query, it prevent the parameter from being replaced.

Here is some sample code:

try {                                                                                                                                                                                                   
    $conn = new PDO("mysql:host=$host;dbname=$db;", $user, $pass);                                                                                                                                      
} catch(PDOException $e) {                                                                                                                                                                              
    echo $e->getMessage();                                                                                                                                                                              
}                                                                                                                                                                                                       

function insert_update_db($query, $params = NULL) {                                                                                                                                                     
global $conn;                                                                                                                                                                                           
    try {                                                                                                                                                                                               
        $sql = $conn->prepare($query);                                                                                                                                                                  
        $sql->execute($params);                                                                                                                                                                         
    }                                                                                                                                                                                                   
    catch(PDOException $e) {                                                                                                                                                                            
        echo $e->getMessage();                                                                                                                                                                          
    }                                                                                                                                                                                                   
}                                                                                                                                                                                                       
$params = array(":a" => 1, ":b" => "test_string");                                                                                                                                                      
$query = "INSERT INTO table1 VALUES (:a, :b)";                                                                                                                                                          
insert_update_db($query, $params);                                                                                                                                                            
$query = "INSERT INTO table1 VALUES (':a', ':b')";                                                                                                                                                      
insert_update_db($query, $params); 

From the general log:

Query INSERT INTO table1 VALUES ('1', 'test_string')
Query INSERT INTO table1 VALUES (':a', ':b')

As you can see from the general log, the second query does not replace the parameters. Why does that happen?

Answer 1

The prepare method is analyzing the SQL query string looking for the replacements (:var or ?). The quoted values are not replaced, otherwise you wouldn't be able to store a literal ':b' value in a table field. Here you have further information about PDO::prepare http://php.net/manual/en/pdo.prepare.php