Why can I use SELECT but not INSERT on a SQLite database in PHP?

Question

I am able to retrieve information from a SQLite database in PHP, but not write to it. For example, this code works perfectly fine:

$db = new PDO("sqlite:foo.db");
$rowCount = $db->query("SELECT COUNT(*) FROM tblname;")->fetchColumn(0);
echo $rowCount; // works as it should

However, the following snippet results in an, 'unable to open database file' error:

$db = new PDO("sqlite:foo.db");
$db->exec("INSERT INTO tblname VALUES(NULL, 'val1', 'val2');") or die(print_r($db->errorInfo(), true));

When I perform the above INSERT from the command line rather than in PHP, I get the expected results (a new row in tblname):

$ sqlite3 foo.db
sqlite> INSERT INTO tblname VALUES(NULL, 'val1', 'val2');

This is my first time using PDOs (or SQLite for that matter) so any help would be greatly appreciated.

Does your web server (apache/php or whatever) have write access to the database? — Daniel A. White
– Daniel A. White, Commented Jan 11, 2010 at 0:12

oops · Accepted Answer · 2010-01-11 00:14:00Z

12

Check that the user running the PHP script has permission to write foo.db.

Also (from memory, correct me if I'm wrong) the user will need write permission on the directory containing foo.db, or else SQLite3 will throw a not-so-useful error message.

answered Jan 11, 2010 at 0:14

oops

5,7991 gold badge20 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Richie Marquez Over a year ago

I made sure the database and the directory it resides in are both writable but that didn't seem to change anything.

oops Over a year ago

Try using an absolute path to foo.db in your code, just to rule out any working directory differences that may be happening...

Richie Marquez Over a year ago

"Check that the user running the PHP script..." That was the problem. My normal user was the one with write permissions, not the user that was actually running the script. Thanks.

siniradam Over a year ago

Wow, it was the folder permission for me, that was unexpected but fixed. Isn't that makes it more dangerous for attacks?

NA. · Accepted Answer · 2010-01-11 00:21:28Z

2

You probobly have no write acccess to the sqllite file. Check the error message you get form PDO with PDO::errorInfo

answered Jan 11, 2010 at 0:21

NA.

6,5999 gold badges38 silver badges36 bronze badges

Collectives™ on Stack Overflow

Why can I use SELECT but not INSERT on a SQLite database in PHP?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related