Escape strings/ inserting in php script

Question

I'm trying to finish a script that connects to two databases, each on a different server, and preforms an update. Basically, the tables being selected from and inserted to are identical: I did a dump/import the other day. The script needs to keep my local table up to date from the remote once since there will be daily records inserted into the remote one and I need to keep it up to date locally.

The key here is that I'm determining the new rows on the remote server by looking at the Auto-incremented Primary key that the tables share, SESSIONID . I'm trying to get my loop below to say, if the id exists in remote server and not local, then insert those records in local server.

I run the below script in powershell by typing php 'filename', and I get both of my successful connection messages, and then I get this message: Incorrect datetime value: '' for column 'ALERTINGTIMESTAMP' at row 1. In this record it's trying to insert, the datetime value is NULL, which the table allows for, however I'm worried it's an issue with escaping characters or something.

How can I modify this to escape properly, or get these records inserted.

Note: Replication and large dump/import/table recreations are not an option for us in this situation. We have several similar scripts to this running and we want to keep the same process here. I'm merely looking to resolve these errors or have someone give me a more efficient way of coding this script, perhaps using a max id or something along those lines.

Here's the script:

      ini_set('memory_limit', '256M');

      // Create connection
      $conn = new mysqli($servername, $username, $password);
      $conn2 = new mysqli($servername2, $username2, $password2);

      // Check connection
      if ($conn->connect_error) {
          die("Connection failed: " . $conn->connect_error);
      }
      echo "Connected successfully";

      // Check connection2
      if ($conn2->connect_error) {
          die("Connection failed: " . $conn2->connect_error);
      }
      echo "Connected successfully";


    $latest_result = $conn2->query("SELECT MAX(`SESSIONID`) FROM `ambition`.`session`");
    $latest_row = $latest_result->fetch_row();
    $latest_session_id = $latest_row[0];

    //Select All rows from the source phone database
    $source_data = mysqli_query($conn, "SELECT * FROM `cdrdb`.`session` WHERE `SESSIONID` > $latest_session_id");

      // Loop on the results
      while($source_item = $source_data->fetch_assoc()) {

          // Check if row exists in destination phone database
          $row_exists = $conn2->query("SELECT SESSIONID FROM ambition.session WHERE SESSIONID = '".$source_item['SESSIONID']."' ") or die(mysqli_error($conn2));

              //if query returns false, rows don't exist with that new ID.
             if ($row_exists->num_rows == 0){

                    //Insert new rows into ambition.session
                    $conn2->query("INSERT INTO ambition.session (SESSIONID,SESSIONTYPE,CALLINGPARTYNO,FINALLYCALLEDPARTYNO,DIALPLANNAME,TERMINATIONREASONCODE,ISCLEARINGLEGORIGINATING,CREATIONTIMESTAMP,ALERTINGTIMESTAMP,CONNECTTIMESTAMP,DISCONNECTTIMESTAMP,HOLDTIMESECS,LEGTYPE1,LEGTYPE2,INTERNALPARTYTYPE1,INTERNALPARTYTYPE2,SERVICETYPEID1,SERVICETYPEID2,EXTENSIONID1,EXTENSIONID2,LOCATION1,LOCATION2,TRUNKGROUPNAME1,TRUNKGROUPNAME2,SESSIONIDTRANSFEREDFROM,SESSIONIDTRANSFEREDTO,ISTRANSFERINITIATEDBYLEG1,SERVICEEXTENSION1,SERVICEEXTENSION2,SERVICENAME1,SERVICENAME2,MISSEDUSERID2,ISEMERGENCYCALL,NOTABLECALLID,RESPONSIBLEUSEREXTENSIONID,ORIGINALLYCALLEDPARTYNO,ACCOUNTCODE,ACCOUNTCLIENT,ORIGINATINGLEGID,SYSTEMRESTARTNO,PATTERN,HOLDCOUNT,AUXSESSIONTYPE,DEVICEID1,DEVICEID2,ISLEG1ORIGINATING,ISLEG2ORIGINATING,GLOBALCALLID,CADTEMPLATEID,CADTEMPLATEID2,ts,INITIATOR,ACCOUNTNAME,APPNAME,CALLID,CHRTYPE,CALLERNAME,serviceid1,serviceid2)
                    VALUES ('".$source['SESSIONID']."' ,
                            '".$source['SESSIONTYPE']."' ,
                            '".$source['CALLINGPARTYNO']."' ,
                            '".$source['FINALLYCALLEDPARTYNO']."',
                            '".$source['DIALPLANNAME']."',
                            '".$source['TERMINATIONREASONCODE']."',
                            '".$source['ISCLEARINGLEGORIGINATING']."',
                            '".$source['CREATIONTIMESTAMP']."',
                            '".$source['ALERTINGTIMESTAMP']."',
                            '".$source['CONNECTTIMESTAMP']."',
                            '".$source['DISCONNECTTIMESTAMP']."',
                            '".$source['HOLDTIMESECS']."',
                            '".$source['LEGTYPE1']."',
                            '".$source['LEGTYPE2']."',
                            '".$source['INTERNALPARTYTYPE1']."',
                            '".$source['INTERNALPARTYTYPE2']."',
                            '".$source['SERVICETYPEID1']."',
                            '".$source['SERVICETYPEID2']."',
                            '".$source['EXTENSIONID1']."',
                            '".$source['EXTENSIONID2']."',
                            '".$source['LOCATION1']."',
                            '".$source['LOCATION2']."',
                            '".$source['TRUNKGROUPNAME1']."',
                            '".$source['TRUNKGROUPNAME2']."',
                            '".$source['SESSIONIDTRANSFEREDFROM']."',
                            '".$source['SESSIONIDTRANSFEREDTO']."',
                            '".$source['ISTRANSFERINITIATEDBYLEG1']."',
                            '".$source['SERVICEEXTENSION1']."',
                            '".$source['SERVICEEXTENSION2']."',
                            '".$source['SERVICENAME1']."',
                            '".$source['SERVICENAME2']."',
                            '".$source['MISSEDUSERID2']."',
                            '".$source['ISEMERGENCYCALL']."',
                            '".$source['NOTABLECALLID']."',
                            '".$source['RESPONSIBLEUSEREXTENSIONID']."',
                            '".$source['ORIGINALLYCALLEDPARTYNO']."',
                            '".$source['ACCOUNTCODE']."',
                            '".$source['ACCOUNTCLIENT']."',
                            '".$source['ORIGINATINGLEGID']."',
                            '".$source['SYSTEMRESTARTNO']."',
                            '".$source['PATTERN']."',
                            '".$source['HOLDCOUNT']."',
                            '".$source['AUXSESSIONTYPE']."',
                            '".$source['DEVICEID1']."',
                            '".$source['DEVICEID2']."',
                            '".$source['ISLEG1ORIGINATING']."',
                            '".$source['ISLEG2ORIGINATING']."',
                            '".$source['GLOBALCALLID']."',
                            '".$source['CADTEMPLATEID']."',
                            '".$source['CADTEMPLATEID2']."',
                            '".$source['ts']."',
                            '".$source['INITIATOR']."',
                            '".$source['ACCOUNTNAME']."',
                            '".$source['APPNAME']."',
                            '".$source['CALLID']."',
                            '".$source['CHRTYPE']."',
                            '".$source['CALLERNAME']."',
                            '".$source['serviceid1']."',
                            '".$source['serviceid2']."')");
                  }
      }

Use parameterised query instead of putting variables in the query. Checkout this link php.net/manual/en/mysqli.prepare.php — Pankaj Makwana
– Pankaj Makwana, Commented Sep 21, 2017 at 13:23
I think that I understand that for the most part (I'm more familiar with the mysql side than the php side). But how exactly would I change all of my existing code? Would I keep the loop? And how would I have to modify my different select/insert statements? — Geoff_S
– Geoff_S, Commented Sep 21, 2017 at 13:50

Aimer · Accepted Answer · 2017-09-21 14:03:30Z

Like Pankaj said, try something like this:

//Insert new rows into ambition.session
$statement = $conn2->prepare('INSERT INTO ambition.session (SESSIONID,SESSIONTYPE,CALLINGPARTYNO,FINALLYCALLEDPARTYNO,DIALPLANNAME,TERMINATIONREASONCODE,ISCLEARINGLEGORIGINATING,CREATIONTIMESTAMP,ALERTINGTIMESTAMP,CONNECTTIMESTAMP,DISCONNECTTIMESTAMP,HOLDTIMESECS,LEGTYPE1,LEGTYPE2,INTERNALPARTYTYPE1,INTERNALPARTYTYPE2,SERVICETYPEID1,SERVICETYPEID2,EXTENSIONID1,EXTENSIONID2,LOCATION1,LOCATION2,TRUNKGROUPNAME1,TRUNKGROUPNAME2,SESSIONIDTRANSFEREDFROM,SESSIONIDTRANSFEREDTO,ISTRANSFERINITIATEDBYLEG1,SERVICEEXTENSION1,SERVICEEXTENSION2,SERVICENAME1,SERVICENAME2,MISSEDUSERID2,ISEMERGENCYCALL,NOTABLECALLID,RESPONSIBLEUSEREXTENSIONID,ORIGINALLYCALLEDPARTYNO,ACCOUNTCODE,ACCOUNTCLIENT,ORIGINATINGLEGID,SYSTEMRESTARTNO,PATTERN,HOLDCOUNT,AUXSESSIONTYPE,DEVICEID1,DEVICEID2,ISLEG1ORIGINATING,ISLEG2ORIGINATING,GLOBALCALLID,CADTEMPLATEID,CADTEMPLATEID2,ts,INITIATOR,ACCOUNTNAME,APPNAME,CALLID,CHRTYPE,CALLERNAME,serviceid1,serviceid2)
VALUES (?, ?, ?, ...);');
$statement->bindParam(1, $source['SESSIONID']);
$statement->bindParam(2, $source['SESSIONTYPE']);
$statement->bindParam(3, $source['CALLINGPARTYNO']);
//...
$statement->execute();

Pankaj Makwana · Accepted Answer · 2017-09-21 14:04:05Z

1

You have to use prepare() function to use parameterized query. Here I have taken example of your query with few parameters you can add yourself with other variables.

$stmt = $conn2->prepare("INSERT INTO ambition.session (SESSIONID,SESSIONTYPE,CALLINGPARTYNO,FINALLYCALLEDPARTYNO) VALUES (:SESSIONID ,:SESSIONTYPE ,:CALLINGPARTYNO ,:FINALLYCALLEDPARTYNO)");

$stmt->bindParam(':SESSIONID', $source['SESSIONID']);
$stmt->bindParam(':SESSIONTYPE', $source['SESSIONTYPE']);
$stmt->bindParam(':CALLINGPARTYNO', $source['CALLINGPARTYNO']);
$stmt->bindParam(':FINALLYCALLEDPARTYNO', $source['FINALLYCALLEDPARTYNO']);
$stmt->execute();

You can checkout this link for more understanding. http://php.net/manual/en/mysqli.prepare.php

answered Sep 21, 2017 at 14:04

Pankaj Makwana

3,0486 gold badges33 silver badges49 bronze badges

3 Comments

Geoff_S Over a year ago

So if I follow that format and just add the other 48 or so column variables in the same fashion, that will still be parameterized? Is this the better practice as opposed to just looping on an array? I'd prefer to do it this way so that I can see all my variables, I'm just unfamiliar so I'm making sure

Pankaj Makwana Over a year ago

Yes, this is parameterized, you can do by looping through or just pasting all the columns whatever you can do

Geoff_S Over a year ago

Ok so I did that exact format and I named all columns correctly. I have a syntax checker that says no issues found but when I run it in powershell it says I have a mysql syntax error at my VALUES (:SESSIONID ,:SESSIONTYPE ,:CALLINGPARTYNO , line

Collectives™ on Stack Overflow

Escape strings/ inserting in php script

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related