map php array keys explicitly to db columns (oracle)

Question

I am passing data from PHP to sql in the form of arrays that look like below:

var_dump($data);

array(
    key_id => 'CLA-ARTCC'(length=9)
    key2 => 'ZLA'length=3)
    key3 => 'LOS ANGELES ACTCC'(length=17)
    key4 => 'ACTCC'(length=5)
    key5 => 'DEA'(length=3)
    key6 => '2555 East Avenue &#39;P&#39;'(length=20) 
    ...
)

I have the started the sql logic like this; which works in some cases but is very error prone, i.e. such as if above array is sent with 'ACTCC' missing it simply pushes 'DEA' value to the 'ACTCC' database column...

example if an array is pushed with no values at key5 (no changes or updates needed to the column); instead it pushes values from incoming key6 as ${data[4]}...

<?php
    .................
    $ds = $_POST['data']; // incoming arrays
    $fd = json_decode($ds, true);

    foreach ($fd as $data) {

                array_values($data)
                $sql = "MERGE INTO app.table a 
                using (SELECT '${data[0]}'    key_id, 
                              '${data[1]}'    key2, 
                              '${data[2]}'    key3, 
                              '${data[3]}'    key4, 
                              '${data[4]}'    key5, 
                              '${data[5]}'    key6, 
                       FROM   dual) p 
                ON ( a.key_id= p.key_id ) 
                WHEN matched THEN 
                  UPDATE SET a.key2= p.key2, 
                             a.key3= p.key3, 
                             a.key4= p.key4, 
                             a.key5= p.key5, 
                  INSERT (key_id, 
                          key2, 
                          key3, 
                          key4, 
                          key5, 
                          key6) 
                  VALUES (p.key_id, 
                          p.key2, 
                          p.key3, 
                          p.key4, 
                          p.key5, 
                          p.key6)";
                          ..........

Also, corrupt character fails, i.e. above at key6 - so how could I map my incoming array keys to explicitly match column names in my oracle db table? *

(while keeping my MERGE INTO WHEN MATCHED INSERT structure below?) sry sql noob...

score -1 · Answer 1 · answered Nov 19 '20 at 01:09

With code that accesses a PHP array by position, you will need to add some PHP validation on the array to make sure it has the correct number of array entries - and that they are in the right order.

Then use bind variables select :1, :2,... instead of string interpolation select '${data[0]}'.... This will reduce SQL injection security risks and aid performance & scalability. It should also help with some of the invalid character issues, though your input data sanitization should do some validation to help stop inserting garbage.

If you are trying to update a column but were passed no value (indicating that no change should be made), you might need to remove that column from the update statement. You might need to dynamically generate the SQL statement. It's a little unclear what constraints you are under, and what you are trying to achieve.

I guess that the invalid SQL e.g the missing WHEN NOT MATCHED THEN clause was just in the StackOverflow snippet. A https://stackoverflow.com/help/minimal-reproducible-example would have been nice.

map php array keys explicitly to db columns (oracle)

1 Answers1