INSERT array - PDO

Question

I've got a portion of code that is supposed to take the data entered in a form, store it in an array and then enter it into the database. I have used var_dump on $fields and $data and they are both returning the information entered in the field (in the add_habbo function). So the problem I've got is that the MYSQL/PDO code isn't inserting this data into the database.

This is the code that I am using to insert them into the database:

    $fields = '`' . implode('`, `', array_keys($habbo_data)) . '`';
    $data   = '\'' . implode('\', \'', $habbo_data) . '\'';

    var_dump($fields);
    var_dump($data);

    global $con;

    $query = "INSERT INTO `personnel` (:fields) VALUES (:data)";
    $result = $con->prepare($query);
    $result->bindParam(':fields', $fields, PDO::PARAM_STR);
    $result->bindParam(':data', $data, PDO::PARAM_STR);
    $result->execute();

I get the impression it has something to with the bindParam sections, possibly PDO::PARAM_STR? Thanks for your assistance!

Update:

$fields = '`' . implode('`, `', array_keys($habbo_data)) . '`';
$fields_data   = ':' . implode(', :', array_keys($habbo_data));

var_dump($fields);
var_dump($fields_data);

global $con;

$query = "INSERT INTO `personnel` (`rank`, `habbo_name`, `rating`, `asts`, `promotion_date`, `transfer_rank_received`, `cnl_trainings`, `rdc_grade`,
    `medals`, `branch`) VALUES ({$fields_data})";
$result = $con->prepare($query);
$result->execute($habbo_data);

$arr = $result->errorInfo();
print_r($arr);

Error:

Array ( [0] => 21S01 [1] => 1136 [2] => Column count doesn't match value count at row 1 )

have you tried echoing out the errorInfo http://www.php.net/manual/en/pdostatement.errorinfo.php to see what the error actually is. BUt the problem is is that you are not binding the fields properly. You are trying to bind all of the fields into 1 parameter, each field has to have its own parameter — Kris, Jul 04 '13 at 16:41
[Insert/update helper function using PDO](http://stackoverflow.com/a/3921798/285587) — Your Common Sense, Jul 04 '13 at 16:49
You should post the real code you used and the real output, your current sql is missing a comma and does not have backticks around the column names. — jeroen, Jul 04 '13 at 17:13
Fixed those two issues, updated to include the code and the new error message. I'm guessing the error is saying the values are still listed as one string rather than individual? — user2547576, Jul 04 '13 at 17:18
Why don't you use the example I have given you? That way the number of fields and variables will always match. Fixed a typo in my answer by the way. — jeroen, Jul 04 '13 at 17:19
Just tried your example again and it worked. Maybe I made an error copying it. Thanks very much for your assistance, greatly appreciated! — user2547576, Jul 04 '13 at 17:23

score 1 · Answer 1 · answered Jul 04 '13 at 16:48

Prepared statements are not the same as copy and paste!

INSERT INTO `personnel` (:fields) VALUES (:data)

You're telling PDO/MySQL here that you want to insert exactly one piece of data (:data) into one field (:field). The value is one string containing commas, not several values separated by commas.

Furthermore you can only bind data, not structural information like field names. You will have to create a query like so:

INSERT INTO `personnel` (foo, bar, baz) VALUES (?, ?, ?)

and then bind data to the three placeholders separately.

jeroen · Accepted Answer · 2013-07-04T17:21:33.053

0

You cannot do that:

You need to add each variable / field-name and value individually;
You can only bind values and not table- or field-names.

Table- and field-names you will have to inject directly into your sql so to prevent sql injection problems, you need to check them against a white-list before doing that.

So in your case that would be something like (rough draft):

// assuming all fields have been checked against a whitelist
// also assuming that the array keys of `$habbo_data` do not contain funny stuff like spaces, etc.
$fields = '`' . implode('`, `', array_keys($habbo_data)) . '`';
$fields_data   = ':' . implode(', :', array_keys($habbo_data));

var_dump($fields);
var_dump($fields_data);

global $con;

$query = "INSERT INTO `personnel` ({$fields}) VALUES ({$fields_data})";
$result = $con->prepare($query);
$result->execute($habbo_data);

Note that I am not manually binding the variables any more but sending the associative $habbo_data array directly as a parameter to the execute method, see example #2.

edited Jul 04 '13 at 17:21

answered Jul 04 '13 at 16:50

jeroen

91,079
21
114
132

Thanks for explaining. I figured it wouldn't be as simple :P. I tried the code you provided and I got this error using errorinfo(): Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near (column names) – user2547576 Jul 04 '13 at 16:59
@user2547576 You'd have to show complete query, perhaps I've made a typo somewhere. Also, what I posted is just the general idea, depending on the array keys of `$habbo_data` it might not be possible to do it like that. – jeroen Jul 04 '13 at 17:01
Oops, I'd mistyped something. I've edited the main post with the new error I'm getting. – user2547576 Jul 04 '13 at 17:09
@jeroen is this type insert PREVENT SQL INJECTION ? – Samir Mangroliya Aug 19 '18 at 04:40

INSERT array - PDO

2 Answers2

Linked

Related