Error:sqlstate[hy093]: invalid parameter number

Question

I can convert the echo'ed output in to an SQL statement that executes in phpMyAdmin going...

From this:

INSERT INTO crumbs (ip_address,ip_address_2,device_info,user_id,connections) VALUES(?,?,?,?,?)Value:'00.000.000.000', '00.000.000.000', 0,0000, 1

Into this:

INSERT INTO crumbs (ip_address,ip_address_2,device_info,user_id,connections) VALUES('00.000.000.000', '00.000.000.000', 0,0000, 1)

It inserts the data in to the DB, no errors, however it executes through PHP-PDO...

With:

SQLSTATE[HY093]: Invalid parameter number

The code:

        $columns = '('.implode(',', array_keys($user_connection)).''.",user_id,connections)";
        $inserts="(".implode(',',array_fill(0,count($user_connection)+2, '?')).")";
        $values = implode(', ',($user_connection)).",$user_id, 1";

        $sql_insert = "INSERT INTO crumbs ".$columns." VALUES".$inserts;
        $stmt = $this->_db->prepare($sql_insert);
        $stmt->execute(array($values));

Edit-Adding $user_connection

$user_connection [ 'ip_address'] = "'".$_SERVER['REMOTE_ADDR']."'";
$user_connection [ 'ip_address_2']="'".$_SERVER['HTTP_X_FORWARDED_FOR']."'";
$user_connection ['device_info']=0;

The error occurs during the execution of the SQL code. I've gone over all the examples and found nothing that's equivalent, I'm thinking it's something simple I'm missing (a rule?) since the code executes locally.

I've posted the structure of $user_connection, I may have an incorrect understanding of it, but I almost exclusively encapsulate my execute commands with 'array' or as above : execute(array($values)); the values turn in to comma separated array values (or so my experience goes). Give me a minute though, I'll try to encapsulate it in a separate array. — Chris, Jul 29 '16 at 14:05
Okay, that worked, I'm confused why array() doesn't work in this instance where it works everywhere else in my code. — Chris, Jul 29 '16 at 14:14
Maybe everywhere else is a bit different.. I can't say for sure without looking at it (Make sure the values being correctly stored in the batabase) — FirstOne, Jul 29 '16 at 14:34

FirstOne · Accepted Answer · 2016-07-29T14:32:30.777

-1

You have to do something like this:

// ..code..
$values = $user_connection;
$values[] = $user_id;
$values[] = 1;
// ..code..
$stmt->execute($values);

Explanation of the problem:

When you have multiple ? placeholders, you can pass each value to be bounded as the values of an array (See Example #3 from the manual).

Now, since you are using implode, $values will be a single string, something like

'192.168.0.1', '8.8.8.8', 0, 'userid', 1

That means that when you call execute(array($values)), it will, in fact, bound it like this (representation-only, it's not really like this)

INSERT INTO crumbs (ip_address,ip_address_2,device_info,user_id,connections) VALUES ("'192.168.0.1', '8.8.8.8', 0, 'userid', 1", ?, ?, ?, ?)

because you only sent and array that has one value: the implosion of the other array. Since you didn't provide the same amount of values (1) as you have placeholders (5), you end up with

Invalid parameter number

edited Jul 29 '16 at 14:32

answered Jul 29 '16 at 14:21

FirstOne

6,033
7
26
45

Well, I can't fix it if you don't let me know what's wrong, @Downvoter – FirstOne Jul 29 '16 at 14:24
My main question would be how to dynamically build the values, strip the quotes from the string when putting in the array? – Chris Jul 29 '16 at 14:35
@Chris I'd suggest you not add the quotes in the first place (<- Is that an option?). When using prepared statments with bound values, you don't have to use quotes... – FirstOne Jul 29 '16 at 14:36
@Chris just note that if you are properly using prepared statments and binding the values, if you have quotes in the string, they will be inserted in the the table, it will not [sql inject](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) you – FirstOne Jul 29 '16 at 14:40
it's not that I'm worried about sql injection, it's more about being lazy, I would like to initiate the values in one point and not have the worry about the rest of the line :) So right now I'm figuring out a function with explode... – Chris Jul 29 '16 at 15:09
After trying a few things, I was able to do minimal changes to the original code, I no longer append and pre-append the quotes to the original ip address information and instead allow the implode automatically do this for me, I also apply the explode to the imploded string to break it up in to individual array values, each one ends up as a string... but insertion in to the db re-casts the int values. – Chris Jul 29 '16 at 15:54

Error:sqlstate[hy093]: invalid parameter number

1 Answers1