CODEWITHSUNDEEP
phpmysqlsql
YMSmash
YMSmash

Reputation: 65

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

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!

Upvotes: 2

Views: 764

Answers (1)

JensV
JensV

Reputation: 4534

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 protected]'

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.

Upvotes: 3

Related Questions