Escaping apostrophe in a dynamically created array in PHP

Question

I'm working on a project to blend a number of different data sets within a PostgreSQL database. I still consider myself a beginner with PHP development and scripting. I am having some real trouble with escaping the apostrophes within the arrays. I tried a few different solutions from these forums: An escaped apostrophe in associative array value, Replace apostrophe in a dynamically created insert statement, http://www.codingforums.com/php/296075-array_walk_recursive-w-function-takes-2-parameters-mysqli_real_escape_string.html, and finally here Escaping quotation marks in PHP. I'm currently trying to recreate my script with a PDO version so I do not have to sanitize my text. At least that is what I understand is the better approach from all of the research I have done. What I'm currently looking for is a method to escape the characters while I find a more eloquent solution. Here is the main piece of code I'm using for the import process:

<?php


include('connect_local.php'); //Includes DB Connection Script
ini_set('max_execution_time', 3000); //3000 seconds = 50 minutes

$emp_get = "SELECT * FROM table1 WHERE person_type LIKE 'Employee'";
$emp_data = pg_query($conn, $emp_get);
while ($emp_row=pg_fetch_array($emp_data)) {


$oraint_get = "SELECT * FROM table2 WHERE source_enrollment_status_name LIKE  'Attended' AND employee_number LIKE '$emp_row[0]' ";
$oraint_data = pg_query($conn, $oraint_get);
$oraint_lms = "Oracle Learning Management Platform";
$oranull = "";


//foreach ($oraint_row as $oraint)
while ($oraint_row = pg_fetch_array($oraint_data)){




    $data_deposit = "INSERT INTO EDU_DATA (person_number, person_name, preferred_name, person_type, start_date, original_date_of_hire
                                 ,hire_date, email_address, region, location, gender, job_name, cbs_level, supervisor_employee_number
                                 ,supervisor_name, supervisor_person_type, business_unit, organization_2, organization_3, effective_date
                                 ,completion_date, training_item_code, days_on_to_do_list, days_overdue, initial_due_in, initial_due_in_unit 
                                 ,retraining_due_in, retraining_due_in_unit, retraining_period, retraining_period_unit 
                                 ,curriculum_code, curriculum_title, learning_course_name, learning_activity, class_duration, college, delivery_method_name
                                 ,class_location_name, class_location_country_name, learning_category, source_enrollment_status_name, lms_platform
                                 ,supervisor_1 ,supervisor_2, supervisor_3, supervisor_4, supervisor_5, supervisor_6, supervisor_7, supervisor_8) 
                          VALUES ('$emp_row[0]','$emp_row[1]','$emp_row[2]','$emp_row[4]','$emp_row[5]','$emp_row[6]','$emp_row[8]'
                                  ,'$emp_row[9]','$emp_row[16]','$emp_row[17]','$emp_row[19]','$emp_row[21]','$emp_row[22]','$emp_row[28]'
                                  ,'$emp_row[29]','$emp_row[30]','$emp_row[33]','$emp_row[44]','$emp_row[45]','$oraint_row[2]','$oraint_row[3]'
                                  ,'$oranull','$oranull','$oranull','$oranull','$oranull','$oranull','$oranull','$oranull','$oranull','$oranull'
                                  ,'$oranull','$oraint_row[4]','$oraint_row[5]','$oraint_row[6]','$oraint_row[7]','$oraint_row[8]','$oraint_row[9]'
                                  ,'$oraint_row[10]','$oraint_row[11]','$oraint_row[12]','$oraint_lms','$emp_row[46]','$emp_row[47]','$emp_row[48]'
                                  ,'$emp_row[49]','$emp_row[50]','$emp_row[51]','$emp_row[52]','$emp_row[53]')";
    pg_query($conn, $data_deposit);

In my attempts to sanitize the text I have tried turning the array output into a string and then using addslashes without any success:

$clnname = $emp_row[1];
addslashes($clnname);

I also tried creating a function to handle this for me recursively using the example I found here: Escape single quotes in every string in php. The code snippet is the following:

 function escapeApos(array $emp_row)
{

    $return_array = [];
    array_walk_recursive($emp_row, function($x) use (&$return_array)
    {

        $return_array[] = str_replace("'","\\'",$x);

    }   

    return $return_array;
}

I have also tried a few other ways without any success. Any aid or assistance will be greatly appreciated. Also with the above function I was not sure if I needed to declare the actual column in the array that I wanted to have sanitized. Again any assistance is welcome! Thank you in advance!

I don't know about PDO but you must normalize that database and bind/sanitize your insert query, and it will automatically do it for you. — matiaslauriti, May 18 '17 at 19:16
I would like to, but I the data manipulation is happening on a PostgreSQL database. I did look up what I believe is the PostgreSQL version and got this error message: "pg_query(): Query failed: ERROR:" Here is the code snippet that I tried: $data = $emp_row[1]; $cln_name = pg_escape_string($data); — Tastedalgory, May 18 '17 at 19:17
@Don't Panic The table is pulling information from five other tables. In the end I'm looking to use Power BI as the data visualization front end. Unfortunately, since you can not blend data in Power BI need to have all of the needed data-points available in one table. — Tastedalgory, May 18 '17 at 19:20
Use prepared statements instead of substituting variables. See `pg_prepare()` and `pg_execute()`, or use PDO. — Barmar, May 18 '17 at 19:23
I'm not sure if I can. Power BI has a requirement of importing the dataset for the visualization in chart format. Can you import a view of data into Power BI? I'll have to check on that. — Tastedalgory, May 18 '17 at 19:28
@Barmar okay, so the PDO route is the better way to go. I find using pg_prepare a bit cumbersome. I think it's because I want to use my actual array name rather than using a string. I looked at the examples for pg_prepare and at the moment I'm not getting how to implement that method. — Tastedalgory, May 18 '17 at 19:56
Are you sure you need to do this in PHP at all? It looks like you should be able to do it entirely in SQL: `INSERT INTO EDU_DATA (...) SELECT ... FROM table1 JOIN table2 ON table1.person_number = table2.employee_number WHERE table1.person_type = 'Employee' AND table2.source_enrollment_status_name = 'Attended'` — Barmar, May 18 '17 at 20:00
I thought about doing it in SQL initially, however I was not sure how to handle the columns that no data would be entered in for. In the example I gave that table has six columns that it will not populate. So, I was not sure how to handle passing null values for those tables. If I understand the INSERT INTO command you have to list the columns in order of how you expect the data to be imported. I decided on using PHP for the extra flexibility. I'm still very new at this, so I appreciate everyone's patience with me. — Tastedalgory, May 18 '17 at 20:33

score 1 · Accepted Answer · answered May 20 '17 at 03:33

Alright, HUGE thanks to everyone for helping me out! I started recreating the script using PDO instead of the first approach I took. Here is a sample of the script, I have some work ahead of me. However, now that I'm using PDO, the issues with sanitizing the text is a non-issue. I'm going to use this method from now on!

<?php

include('connect_local_pdo.php'); //Includes DB Connection Script
ini_set('max_execution_time', 3000); //3000 seconds = 50 minutes

try {
    $stmt = $conn->query('SELECT * FROM table1');
    $rows = $stmt->setFetchMode(PDO::FETCH_ASSOC);

    while ($rows = $stmt->fetch()) {

        $emp_id = $rows['person_number'];

        $stmt2 = $conn->query("SELECT * FROM table2 WHERE employee_number LIKE '$emp_id'");
        $oracleint = $stmt2->setFetchMode(PDO::FETCH_ASSOC);

        while ($oracleint = $stmt2->fetch()) {



            $GO = $conn->prepare("INSERT INTO table3 (person_number, person_name, learning_course_name) VALUES (:emp_number, :emp_name, :learning_course_name)");

            $GO->bindParam(':emp_number', $rows['person_number']);
            $GO->bindParam(':emp_name', $rows['person_name']);
            $GO->bindParam(':learning_course_name', $oracleint['learning_course_name']);

            $GO->execute();

        }


        }

    } catch (PDOException $b) {
        echo 'Data Extraction Failed: ' . $b->getMessage();

    }

Again, thanks for assisting the newbie! I totally love StackExchange!! You guys ROCK!

Escaping apostrophe in a dynamically created array in PHP

1 Answers1