Invalid parameter number?

I’m having one of those :banghead: moments right now.
I’m working on a form script (PHP) that inserts or updates a database, and it’s failing but I don;t see why.
Clearly there is something really dumb that I’m not seeing, but I’ve been looking too long now and can’t spot it, maybe someone else will.
This is the query string. It’s built dynamically, adding the fields and ?s depending which table it’s used on.

"INSERT INTO classes (title, class, descr, start, notb, locat, subcat, prize1, prize2, prize3, prize4, prize5, total, fee, tbl, speed, height, rule, hh) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

This is a var_dump of the array that goes into the execute command.

array (size=19)
  'title' => string 'New Class Test' (length=14)
  'class' => string '99' (length=2)
  'descr' => string 'Testing!!' (length=9)
  'start' => string '' (length=0)
  'notb' => string '0' (length=1)
  'locat' => string '' (length=0)
  'subcat' => string '3' (length=1)
  'prize1' => string '150' (length=3)
  'prize2' => string '' (length=0)
  'prize3' => string '' (length=0)
  'prize4' => string '' (length=0)
  'prize5' => string '' (length=0)
  'total' => string '' (length=0)
  'fee' => string '' (length=0)
  'tbl' => string '' (length=0)
  'speed' => string '' (length=0)
  'height' => string '' (length=0)
  'rule' => string '' (length=0)
  'hh' => string '' (length=0)

And this is the error I get every time.

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

I may have forgotten how to count, but I think there are 19 of everything, isn’t there?

what’s the PK of the table? is it auto_increment?

Yes, there is an auto incrementing id field as the PK.

okay, let’s try a different approach

run this directly in mysql (i.e. not via php) and see what you get…

INSERT INTO classes ( title , class , descr , start , notb , locat , subcat , prize1 , prize2 , prize3 , prize4 , prize5 , total , fee , tbl , speed , height , rule , hh ) VALUES ( 'New Class Test' , '99' , 'Testing!!' , '' , '0' , '' , '3' , '150' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' )

1 Like
#1292 - Incorrect datetime value: '' for column 'start' at row 1

I think I maybe need to alter the script to make the empty strings NULL.
start is of course a datetime, but is not mandatory on the form, so can be null.

and you’re on your way :slight_smile:

That hasn’t fixed it :disappointed:
But using the direct query is better for debugging, with a more specific error message. :+1:
I’m out of time tonight, but will continue this tomorrow.

I’ve not made a lot of progress here, still stumped by it.
I did discover on the classes table there was a column the form does not populate which had no default. I fixed that by adding a default, but didn’t help. I then dropped that column, as it was not needed and that did not work. There was another column not defined by the form which did have a default, I added that to the query to explicitly set a value for it, but it still gives me the error. That is when running the script. If I put the query in directly, like yours above, it works fine.

The script should deal with any one of three tables. So to simplify things I’m now trying with the least complex, or smallest one, instead of the biggest one like before.
Here we see the table structure:-

And the query:-

INSERT INTO compcats (title, notes, image) VALUES (?, ?, ?)

The array var_dump:-

  'title' => string 'New Category' (length=12)
  'notes' => string 'This is a test!' (length=15)
  'image' => null

And the error:-

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

So the only thing I am not defining is the PK (id) which I don’t think I should have to.
Any ideas?

can’t help you with the php, sorry – try the php forum

as for the sql, what happens when you run this –

INSERT INTO compcats ( title , notes , image ) VALUES ( 'New Category' , 'notes' , NULL )

That works.

It’s possible it could be a PHP/PDO thing, but the script should effectively do exactly the same as your above query, I can’t make any sense of it.
I may move to the php forum and see if anyone has an idea.

I’ve moved this to PHP to see if it gets fresh eyes.

Here is an excerpt from the php script:-

                                $farray = explode(', ', $fields) ; // $fields is just a comma separated list of table columns
				foreach( $farray as $field ){
					if($action == 'edit'){ $blanks[] = "$field = :$field" ;}
					else{ $blanks[] = '?';}
				}
				$tabledata = $formresults ; // An array of data from the form, with keys corresponding to table columns, see dump
				$blanks = implode(', ', $blanks);
				
				if($action == 'edit'){ // is an update
					require_once $incpath."include/conn/edit-pub.php" ; // connection $db
					$tabledata['id'] = $iid;
					$query = "UPDATE $table SET $blanks WHERE id = :id";
				}
				else{ // Is an Insert
					require_once $incpath."include/conn/insert-pub.php" ; // connection $db
					$query = "INSERT INTO $table ($fields) VALUES ($blanks)";
				}
				try{
					$sql = $db->prepare($query);
					$sql->execute($tabledata);
					if($action == 'add'){ $iid = $db->lastInsertId('id'); } // get the new id
				}
				catch(PDOException $e){
					file_put_contents($pdoelog, '['.date('Y-m-d H:i:s').'] '.$e->getMessage()."\n", FILE_APPEND);
					// Temp for debugging
					echo "<p>$query</p>" ;	
					echo var_dump($tabledata);
					echo $e->getMessage() ;
					exit(" - DB Error!");
				}

Any idea why this gives an error every time?

[OT] Before anyone jumps on my for the variables in the queries, I assure you the values are hard coded and clean.

if the query works outside of php, and fails inside of php, it is guaranteed more than “possible”

This is a var_dump of the array that goes into the execute command.

make it

$stmt->execute(array_values($array));

For the positional placeholders you have to provide a non-associative array
And you cannot mix positional and named placeholders in the same query.

Also your edit code could be vulnerable to SQL injection depends on the source of the keys

1 Like

Make sure you are not near any walls because I suspect you head cannot take much more banging.

start is a reserved word: https://dev.mysql.com/doc/refman/5.5/en/keywords.html

Back ticks might help.

You might also take a look at the Doctrine’s Database Abstraction Layer, http://www.doctrine-project.org/projects/dbal.html, which would reduce your code to:

$db->insert('classes',$data);

:bulb: Bingo!
That’s what I was missing. I thought I had done this before many times, but the form result is an associative array with keys. :smile:

Thanks for the concern, but I believe I have it covered.
with “edit” you see $blanks being populated from $fields which is a comma separated list, no keys. The lists are hard coded into an array elsewhere in the script, untouched by the user.
The form results array does of course have user input in the values, sanitised and validated. The keys, which as you point out are surplus to requirement here, are named as form input names, but those key names come from the table that holds the form inputs data that creates the form, not the post array that results from the form. Any form fields in post not stored in that table, will not be processed.
In addition note the different connection includes called. The DB users are “one trick ponies” with very limited privileges for a certain task, in the event of an injection, the worst they could do is insert junk.

1 Like

According to the link you posted, it is not.

Table 9.2 Keywords and Reserved Words in MySQL 5.5

Oops. Got me. start is a keyword but not a reserved word so no back ticks required.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.