INSERT array - PDO [duplicate]

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 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
– Kris, Commented Jul 4, 2013 at 16:41
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
– jeroen, Commented Jul 4, 2013 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
– user2547576, Commented Jul 4, 2013 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
– jeroen, Commented Jul 4, 2013 at 17:19

deceze · Accepted Answer · 2013-07-04 16:48:59Z

1

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.

answered Jul 4, 2013 at 16:48

deceze♦

525k89 gold badges807 silver badges954 bronze badges

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

Comments

jeroen · Accepted Answer · 2013-07-04 17:21:33Z

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 4, 2013 at 17:21

answered Jul 4, 2013 at 16:50

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

4 Comments

user2547576 Over a year ago

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)

jeroen Over a year ago

@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.

user2547576 Over a year ago

Oops, I'd mistyped something. I've edited the main post with the new error I'm getting.

Samir Mangroliya Over a year ago

@jeroen is this type insert PREVENT SQL INJECTION ?

Collectives™ on Stack Overflow

INSERT array - PDO [duplicate]

2 Answers 2

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Linked

Related