-1

Hello I've tried both these questions solutions (final goal added at bottom)

INSERT array - PDO

Binding values from arrays?

but I don't get the expected variables content in $fields and $newdata

so I kindly print here some var_dump and cast to kindly ask your support.

My array derivate from an html table

For simplicity in my learning experiment I'm working with a dummy table of just 5 fields, as you see they are: selected, user_id, user_name, user_company and user_email. Finally I have inserted just 2 rows of values.

The table content is posted as JSON.stringify.

Here you my results

Using the usual

print_r ( $Arr );

I can see this output

Array ( 
[0] => Array ( [selected] => [user_id] => 3 [user_name] => nome3 [user_company] => azien3 [user_email] => email3 ) 
[1] => Array ( [selected] => 1 [user_id] => 6 [user_name] => nome6 [user_company] => azien6 [user_email] => email6 ) 
)

next I try to apply the code of from the two above questions

24  $fields = implode(",", array_keys($Arr));
25  $newdata = "'" . implode("','", $Arr) . "'";
26  
27  var_dump($fields);
28  echo "<br><br>";
29  var_dump($newdata);

But something is wrong in my interpretation or in my code , because the output is

Notice: Array to string conversion in D:\xampp\htdocs\ajax-json\post.php on line 25

Notice: Array to string conversion in D:\xampp\htdocs\ajax-json\post.php on line 25
string(3) "0,1"

string(15) "'Array','Array'"

can you kindly point out what's wrong? e.g. is my array properly formed?

the final goal is to build a query where they are bind the keys names and key values taken from the associative array directly to columns and values for an INSERT into a mysql table.

In other words since the array's keys names are identical to the database table's columns names, I'm wondering how to make an automatism that creates the query like in the two questions in the opening of this question.

With "automatism" is meant to HAVE variables and maybe cycles to build a query INSTEAD than writing the single columns names and the same for the columns values to be inserted


Edit: from the accepted answer, this is the working code.

$my_keys = array_keys($Arr[0]);

// ---- This prevents PDO SQL Injection
$stmt=$pdo->prepare("DESC my_table");
$stmt->execute();
$whitelist_columns=$stmt->fetchAll(PDO::FETCH_COLUMN);
foreach($my_keys as $key){
    if(!array_search($key,$whitelist_columns)){ echo "ERROR!"; }
}
// ---- End of prevention

$field_names = implode(",", $my_keys);   // build column list

/** @JBH this foreach is needed otherwise the $q_markers will result not PDO placeholders like.
If this is missing, the query inserts all "" values, no matter if you'll adopt bindValue or bindParam**/
foreach($my_keys as &$key){
    $key = ":".$key; 
}
$q_markers = implode(",", $my_keys);     // build PDO value markers

$stmt = $pdo->prepare("INSERT INTO my_table (".$field_names.") VALUES (".$q_markers.")");
foreach($Arr as $key => $val){
    foreach($val as $bind_marker => &$bind_val){   /** @ JBH Without "&" here, it will work 
only bindValue. Instead with "&", they work both bindParam and bindValue **/
        $stmt->bindParam($bind_marker, $bind_val);  
    }
    $stmt->execute();
}
4
  • What are you expecting in $newdata? You're trying to implode two arrays, which you cannot do. Are you trying to implode each array? $Arr[0] and $Arr[1]? Even that's not right. You can't implode an associative array. Please type out an example of what you expect to see. Commented Jul 28, 2017 at 8:09
  • $Arr is a multidimensional array. It behaves not how you expect. Commented Jul 28, 2017 at 8:12
  • Mmmh the final goal is to build a query where they are bind the keys names and key values taken from the associative array directly to columns and values for the mysql table. In other words since the keys names are identical to the database table's columns names, I'm wondering how to make an automatism that creates the query like in the two links at top. Commented Jul 28, 2017 at 8:19
  • @JBH thank you for replying. What I want to achieve is what is answered in the two links at top. Read my comment on top of this. With "automatism" is meant to HAVE variables and maybe cycles to build a query INSTEAD than writing the single columns names and the same for the columns values to be inserted Commented Jul 28, 2017 at 8:22

1 Answer 1

1

You can implode an associative array, but you cannot implode a multi-dimensional array. That's what the error is telling you. For example...

$my_array = array('a'=>'1', 'b'=>'2', 'c'=>'3');
echo "\n\n".implode(',',array_keys($my_array));
echo "\n\n".implode(',',$my_array)."\n\n";

Results in...

a,b,c

1,2,3

But...

$my_array = array(
    array('a'=>'1', 'b'=>'2', 'c'=>'3'),
    array('d'=>'4', 'e'=>'5', 'f'=>'6')
);
echo "\n\n".implode(',',array_keys($my_array));
echo "\n\n".implode(',',$my_array)."\n\n";

results in...

0,1
PHP Notice:  Array to string conversion in /test.php on line 9

Fixing your code means dealing with the individual data elements. Echo'd out they'd look like this:

selected, user_id, user_name, user_company, user_email
,3,nome3,azien3,email3
1,6,nome6,azien6,email6

So, the basic code would look something like...

$fields = implode(",", array_keys($Arr));
echo $fields."\n";
foreach($Arr as $key=>$val){
    $newdata = "'" . implode("','", $Arr[$key]) . "'";
    echo $newdata."\n";
}

And a PDO INSERT statement would be built like this...

$my_keys = array_keys($Arr[0]);

$stmt=$pdo->prepare("DESC my_table");
$stmt->execute();
$whitelist_columns=$stmt->fetchAll(PDO::FETCH_COLUMN);
foreach($my_keys as $key){
    if(!array_search($key,$whitelist_columns)){ echo "ERROR!"; }
}

$field_names = implode(",", $my_keys);       // build column list
$q_markers = implode(",", $my_keys);     // build PDO value markers

$stmt = $pdo->prepare("INSERT INTO my_table (".$field_names.") VALUES (".$q_markers.")");
foreach($Arr as $key => $val){
    foreach($val as $bind_marker => $bind_val){
        $stmt->bindParam($bind_marker, $bind_val);
    }
    $stmt->execute();
}

Note the section of code with the whitelist variables. The purpose of that code is to protect against SQL injection due to creating the query with unbound column references. PDO does not allow you to bind column names in the same way it does cell data. To protect yourself you must prove that the incoming data matches the columns in the table. If they don't, do something about it (echo "ERROR";). Usually you want to stop that INSERT completely and log the issue somewhere.

$my_keys = array_keys($Arr[0]);
$q_marks = array();

$stmt=$pdo->prepare("DESC my_table");
$stmt->execute();
$whitelist_columns=$stmt->fetchAll(PDO::FETCH_COLUMN);
foreach($my_keys as $key){
    if(!array_search($key,$whitelist_columns)){ echo "ERROR!"; }
    array_push($q_marks, "?");
}

$field_names = implode(",", $my_keys);       // build column list
$field_markers = implode(",", $q_marks);

$stmt = $pdo->prepare("INSERT INTO my_table (".$field_names.") VALUES (".$field_markers.")");

foreach($Arr as $key => $val){
    $stmt->execute($val);
}

The above code is an example of using PDO without bindParam or bindValue. It comes with a price, though usually there's no actual cost. bindParam and bindValue allow you to specifically identify the data type. E.G., bindParam('myval', $myval, PDO::PARAM_INT). When variables are passed as above, you can't do this. Most of the time this is a non-issue as PHP correctly identifies the data type. When PHP does become confused (or if you simply want to impose a check that the data is what you were expecting), then you must use bindParam or bindValue.

Sign up to request clarification or add additional context in comments.

23 Comments

Thank you JBH, I'm impatient to try it out (currently I'm away from home). It seems exactly the right process. This question derives from this other question I've opened but it has been marked as duplicate stackoverflow.com/questions/45349503/… and there I was mentioning PDO as preferred choice! You read my mind ;-) (though maybe PDO is the only choice for this situation (?))
PDO is always the best choice as it protects you against SQL injection. You should always use PDO.
That's my goal, I always use PDO, having to learn, I understood it was the one to learn against msqli. I feel comfortable with PDO and it makes things really much readable while if properly implemented, it makes the structure more effective thin lightweight and straightforward e.g. with loops when preparing statements (if needed). E.g. I'm wondering how do they do without PDO placeholders (asked like "how do they do without coffee" :-)).. we know it can be done, but what a mess. Nevertheless PDO prosecutes the object concept which is something to take habit with in perspective.
About PDO though here phpdelusions.net/pdo/sql_injection_example is explained that one SQL injection is possible either if prepared statements are used ... but... contextually is explained how to avoid that :-)
That's a good point. I'd forgotten we're building the column name list dynamically. I've edited my answer to include Whitelist code that protects against this kind of injection.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.