prepared statement isn't working

Forum Moderators: open

Message Too Old, No Replies

prepared statement isn't working

Skier88

3:26 pm on Sep 17, 2010 (gmt 0)

I'm converting my scripts to prepared statements for the added security, but I've run into a problem so simple I don't even know how to troubleshoot it.

This code runs (the if statement returns true), but does not add an entry:


if($stmt->prepare("INSERT INTO ratings VALUES ('',?,?,?,?,?,?)"))
{
$stmt->bind_param('ssssis',$ip,$article,$date,$author,$rating,$comments);
$stmt->execute();
}

This code also runs, and successfully adds a row:


mysql_query("INSERT INTO ratings VALUES ('','$ip','$article','$date','$author','$rating','$comments')");

Both methods are able to initialize, and the same script contains other identically formatted prepared statements that function perfectly.

Any suggestions? Thanks for reading.

Skier88

6:17 pm on Sep 17, 2010 (gmt 0)

Just re-reading, and my first post is a little unclear. I should have said similarly (not identically) formatted statements. What I meant was that statements of this format work:


if($stmt->prepare("[query]"))
{
$stmt->bind_param('[types]',[variables]);
$stmt->execute();
}

Also, I don't think this is the problem, but none of the working statements insert a row - they are either SELECT or UPDATE queries.

enigma1

11:45 am on Sep 30, 2010 (gmt 0)

It's not possible to know what these member functions are doing without looking at the db class code. And it's not too efficient to have several lines to perform a query and somehow you need to validate the input fields by type and perhaps by value.

Skier88

7:47 pm on Oct 5, 2010 (gmt 0)

Thanks for the reply enigma. Yes, I've been working on security, I was just giving preliminary code.

Anyway, I solved my problem. It turns out prepared statements don't work if you bind a value to a variable which is null. It works if you set it equal to ''. So my fix was replacing


$author=$_REQUEST['author'];

with


$author=$_REQUEST['author']?$_REQUEST['author']:'';

(security etc removed for clarity's sake)