Welcome to WebmasterWorld Guest from 54.196.100.224

Forum Moderators: open

Message Too Old, No Replies

prepared statement isn't working

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

Junior Member

5+ Year Member

joined:July 30, 2009
posts: 130
votes: 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.
6:17 pm on Sept 17, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:July 30, 2009
posts: 130
votes: 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.
11:45 am on Sept 30, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Apr 30, 2007
posts:1394
votes: 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.
7:47 pm on Oct 5, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:July 30, 2009
posts: 130
votes: 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)