Can't escape single quotes inside an array even after PDO prepare statement and bind params

Question

I'm working with two identical tables from SQL Server and MySQL and my end-goal is to be able to sync their contents through PHP. I made a previous post about this and I found out that the reason my code wasn't working was because of single quotes messing up my SQL Syntax. I then converted my table to use PDO instead because I heard preparing statements/binding param through it is more efficient. However, my code is still not escaping single quotes properly. I've already look into past posts but none of them solved my problem. Here is the code:

<?php

$serverName = "<servername>";
$connectionInfo_mssql = array("Database"=>"<dbname>", "CharacterSet"=>"UTF-8");

try
  {
    $conn_mssql = new PDO("sqlsrv:Server=$serverName;Database=<dbname>");
    $conn_mssql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $conn_mysql = new PDO("mysql:host=localhost;dbname=<dbname>", "", "");
    $conn_mysql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    //SELECT FROM SQL SERVER DB
    $mssql_array = array();
    $mssql_query = $conn_mssql->prepare("SELECT * FROM Customers");
    $mssql_query->execute();

    while($row = $mssql_query->fetch(PDO::FETCH_BOTH))
    {

      $mssql_array[] = array('ID' => $row['ID'], 
                             'Name' => $row["Name"], 
                             'Address' => $row['Address'], 
                             'Email' => $row['Email']);

    }

    foreach($mssql_array as $key => $value)
    {

       //SELECT FROM MySQL DB
       $mysql_query = $conn_mysql->prepare("SELECT COUNT(*) FROM Customers WHERE ID ='".$value['ID']."' 
                                                        AND Name = '".$value["Name"]."' 
                                                        AND Address = '".$value['Address']."' 
                                                        AND Email = '".$value['Email']."' ");
       $mysql_query->execute();
       $num_rows = $mysql_query->fetchColumn();

       if ($num_rows == 0)
                       {
                         //INSERT INTO MySQL DB
                         $sql = $conn_mysql->prepare("INSERT INTO Customers VALUES (:ID, :Name, :Address, :Email)");

                         $params = array(':ID' => $value["ID"], ':Name' => $value["Name"], ':Address' => $value["Address"], ':Email' => $value["Email"]); 
                         $sql->execute($params); //this is where the error occurs
                       }
    }

    echo 'Table Customers from MS SQL DB and table Customers from MySQL DB are now synced!'."<br>";
    echo "<a href='table_updater.php'>Go back to updater</a>";

  }
catch(PDOException $e)
  {
    echo "Error: " . $e->getMessage();
  }

?>

What this basically does is it selects all of the SQL Server table's contents and puts it in MySQL, but since one of my rows has the value "Jojo's" it just gives me an error because of the single quote. The error I'm getting is

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's' AND Address = 'IDK''

which pretty much tells me that I'm not escaping the single quote.

Any ideas are much appreciated!

Use a prepared statement with placeholders, and you won't get these issues. — Qirel, Apr 23 '19 at 11:12
I don't know PHP or MySQL (I'm only here because you tagged SQL Server incorrectly), however, that looks like you're injecting your parameter values. Parametrise your query, and the problem goes away. This is a great example of an XY problem: you *think* the issue is the single quotes, but it's actually the way you are building your statement is wrong. — Thom A, Apr 23 '19 at 11:12
little hint: SQL Server supports a [linked server](https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017) to MySQL... Which you can [query](https://stackoverflow.com/questions/16687999/select-from-linked-mysql-server) — Raymond Nijland, Apr 23 '19 at 11:19
For your SELECT, you qre not using prepared statements because you don't use placeholders — nacho, Apr 23 '19 at 11:19
@Larnu Oh, did I do it wrong then? I thought I was already using named placeholders (hence the :ID, :Name, etc) in my prepared statement and bound them into an array — YMSmash, Apr 23 '19 at 11:22
@YMSmash you are using them in the INSERT statement, but not for the SELECT statement — JensV, Apr 23 '19 at 11:24
@nacho so it's not enough to only be using prepare for the INSERT part? thanks, I'll try to fix it and see what happens — YMSmash, Apr 23 '19 at 11:26
No, it's not enough. You should use prepared statements every time you access your DB, because, in all cases, you can get SQL injection — nacho, Apr 23 '19 at 11:40

score 3 · Accepted Answer · answered Apr 23 '19 at 11:27

The issue is not with the INSERT statement but with the SELECT one. From the error you can see that it fails to interpret a part of the select query. So the correct code would look like this:

//SELECT FROM MySQL DB
$mysql_query = $conn_mysql->prepare("SELECT COUNT(*) FROM Customers WHERE ID = :ID
                                                        AND Name = :Name
                                                        AND Address = :Address
                                                        AND Email = :Email ");

$params = [':ID' => $value['ID'], ':Name' => $value['Name'], ':Address' => $value['Address'], ':Email' => $value['Email']];
$mysql_query->execute($params);

To further explain, without the placeholders, your select query could look like the following if for example the name had a quote in it:

SELECT COUNT(*) FROM Customers WHERE ID = '123'
                            AND Name = 'As'd, dfg'        # <- Problem here
                            AND Address = 'some address'
                            AND Email = 'email@example.com'

For the record, you should always use placeholders for any value that you do not control in code. Even if that's for a SELECT statement or a data source you trust. This prevents unintended injection by accident and handles any type of character.

Thanks @JensV and everyone else, this line of code worked for me, I didn't realize that I had to do the same for the SELECT statement; turns out that's where the error occurs — YMSmash, Apr 23 '19 at 11:34

Can't escape single quotes inside an array even after PDO prepare statement and bind params

1 Answers1