Insert/update query on SQLite database with PDO works on local, but fails silently online

Question

I have noticed the following very strange behaviour. I insert/update data from a webform into an SQL database with PDO. I have a number of different pages, some of which contain image upload fields.

Now for the strange thing: I use the following method to insert/update the database:

// For one of the pages with an image upload (works perfectly on localhost as well as on a server):

// For a new insert
$query = "INSERT INTO ZCATEGORIES (ZIMAGE, ZIMAGETYPE, ZNAME, ZCOLOR) VALUES ('?', 'image/png', 'newCat', '#F78A9FF')"; 

// When there is already something in the db under the id Z_PK
$query = "UPDATE ZCATEGORIES SET ZIMAGE='?', ZIMAGETYPE='image/png', ZNAME='newCat', ZCOLOR='#F78A9FF', WHERE Z_PK='1'";
$sth = $db->prepare($query);          // $query is a correctly formatted SQL query string, I have checked that thoroughly

// Image validation is done earlier on the page
if (is_uploaded_file($_FILES[$fieldname]['tmp_name'])
    $img = file_get_contents($_FILES[$fieldname]['tmp_name']);
    $sth->bindParam(1, $img, PDO::PARAM_LOB);
}

$db->beginTransaction();
$sth->execute();
$db->commit();
$sth->closeCursor();

The following causes problems:

// For one of the pages without an image upload (works on localhost but NOT on a server):

// For a new insert
$query = "INSERT INTO ZCUSTOM (ZFONT, ZTEXTCOLOR, ZBUTTONCOLOR, ZBUTTONTEXTCOLOR) VALUES ('Monaco', '#FF0000', '#1CFF49', '#F78A9FF')"; 

// When there is already something in the db
$query = "UPDATE ZCUSTOM SET ZFONT='Monaco', ZTEXTCOLOR='#FF0000', ZBUTTONCOLOR='#1CFF49', ZBUTTONTEXTCOLOR='#F78A9FF'";
$sth = $db->prepare($query);          // $query is a correctly formatted SQL query string, I have checked that thoroughly

$db->beginTransaction();
$sth->execute();
$db->commit();
$sth->closeCursor();

But this DOES work again on both localhost and server

// For one of the pages without an image upload (works both on localhost and on a server):

// For a new insert
$query = "INSERT INTO ZCUSTOM (ZFONT, ZTEXTCOLOR, ZBUTTONCOLOR, ZBUTTONTEXTCOLOR) VALUES ('Monaco', '#FF0000', '#1CFF49', '#F78A9FF')"; 

// When there is already something in the db
$query = "UPDATE ZCUSTOM SET ZFONT='Monaco', ZTEXTCOLOR='#FF0000', ZBUTTONCOLOR='#1CFF49', ZBUTTONTEXTCOLOR='#F78A9FF'";
$db->query($query);

Now the above method works perfectly on my localhost. Both the pages which contain text input only and the pages which contain text as well as an image get stored in the database successfuly. However the problem arises when trying to insert/update the database on a webserver. Then for some reason, the form submit on the pages containing text and image data still works like expected. However, nothing gets inserted into the db on the pages only containing text data...

After numerous hours of desperately trying out different things, I decided to exchange the whole prepare, beginTransaction, execute... procedure on these troublesome pages with a simple

$db->query($query);

and much to my surprise now it works fine online! Is there something I'm missing here? I don't understand why this particular problem has anything to do with trying things out locally or via a webserver... Why should it all work flawlessly on my localhost, but cause problems online? And bizarly, only for ceratin pages? I've checked that both the db and the folder it's in are both readable and writeable. I'm on mac OSX 10.6.7 and am running PHP version 5.3.4

If anyone has an idea what might cause the problems, then please share. I would be very grateful.

MJD: Isnt' a bind param missing if no file is uploaded? Probably transaction takes this a bit more strict. Do the mysql server versions differ between production / dev? — hakre, Jun 16 '11 at 09:43
I don't think you need bindParam() when you haven't got any input variables, right? $sth = $db->prepare($query); $sth->execute(); should work. — MJD, Jun 16 '11 at 10:20
Try dumping out the SQLCODE after the beginTransaction, execute, etc. I'm guessing that somewhere along the line something is failing, and that there's a message that can help you figure it out. — Bob Jarvis - Слава Україні, Jun 16 '11 at 11:37
I have updated my original post. My queries are actually generated dynamically, so this is just a typical example. However, the queries realy aren't the problem. I have executed them manually in MesaSQLite to check if they're valid and then the db updates without any problems — MJD, Jun 16 '11 at 11:38
Alright... I have put in some echo's and dumps. The beginTransaction() call returns TRUE, the execute() call returns TRUE, but the commit() call returns nothing. — MJD, Jun 16 '11 at 12:03

score 0 · Answer 1 · answered Jul 06 '11 at 16:22

I've checked that both the db and the folder it's in are both readable and writeable.

I managed to solve a similar problem by making the entire path to the database (at least, from your home directory) readable and executable, in addition to making the folder the DB is in readable and writeable.

(Note: I'm talking about changing permissions on the server, not on your local machine.)

For example, if your site lives in

~/Sites/mysite

and your database is located at

~/Sites/mysite/db/main.db

you could use the following commands in BASH to set the appropriate permissions:

$ chmod a+xr ~/Sites
$ chmod a+xr ~/Sites/mysite
$ chmod -R a+xrw ~/Sites/mysite/db/

Hope this helps.

Insert/update query on SQLite database with PDO works on local, but fails silently online

1 Answers1