Cannot insert into SQLite database through php PDO

Question

Pls help see what is wrong.... (I test the db connection is fine)

<?php
$user_name=$_POST['user_name'];
$password=$_POST['password'];

$dbh=new PDO('sqlite:./db/user.db') or die("fail to connect db");

try{
 $stmt = $dbh->prepare("INSERT INTO user_info VALUES (?, ?)");
 $stmt->bindParam(1, $a);
 $stmt->bindParam(2, $b);
 $a=$user_name;
 $b=$password;
 $stmt->execute();
}
catch(PDOException $e) {echo $e->getMessage();}

?>

Solution

I have had time to reconsider this answer after discussion with @DerrickCoetzee in the comments. The truth is, the answer may in fact be the only way to fix this problem on some servers. I had suggested to give universal write access to the database, which is admittedly unsafe. The reason, I have found, why that was the only solution I could have is that my server has disabled write access for php. php is incapable of making new files, and I am unable to change the owner of the database to apache or "nobody" without root access, which makes a better solution impossible.

Essentially, my server has locked down php such that it can only write if everyone can write. In my opinion, this is a bad server setup, which led to this security risk and as @Derrick suggests, an sqlite db should probably only be used for private use or server-access-only. Some systems are not setup to take advantage of this safely. If you find php has no write access and you do not have root access to the machine, you should consider contacting your system administrator or switch to MySQL if it's available.

That being said, I find the following solution very handy for quick, prototyping solutions, when the db is not sensitive, or will only be known about by a small select group of people.

---

My previous answer:

I had this exact same problem today! I am pretty new to php and sqlite, but was chugging along. All of a sudden I hit this massive roadblock (no progress for a day) from lack of insert into my sqlite database as described by the question poster. The distinguishing factor here is that there is no error message or exception, only the return of false if you put the execute statement into an if clause.

So, I was finally able to figure out a way to get insertion to work, but I am not 100% certain if it is safe or not. I'd appreciate any comments if you think there's a better way.

The main problem is that you are trying to let your users write to a file on your file system. However, by default folders and documents only have read access for outsiders. Therefore, you need to give both your database (user.db) and it's folder (./db) writable access others.

So, you need to go the directory and type in:

chmod 777 ./db
chmod 766 ./db/user.db

That fixed this problem for me, so I hope it helps you out, too. I don't know if there is a better way or not, but it seems rather logical.

Comments

[lkahtz]

I changed the code as follows: <?php $a=$_POST['user_name']; $b=$_POST['password']; //still not working... $dbh=new PDO('sqlite:./db/user.db') or die("fail to connect db"); try{ $stmt = $dbh->prepare("INSERT INTO user_info ('user_name', 'password') VALUES(?, ?)"); $stmt->bindParam(1, $a); $stmt->bindParam(2, $b); $stmt->execute(); } catch(PDOException $e) {echo $e->getMessage();} ?>

[Christian Joudrey]

INSERT INTO user_info(username, password) not 'username', 'password'

[David Powers]

@Christian Joudrey is right. Remove the quotes from around the column names. Then it should work.

[Chiara Coetzee]

Unless all users on your system are trusted, which is usually a bad assumption, making any database file world-writable is very risky. Any user with an account (or who has compromised any account) could destroy the entire database easily. If a database really needs to be shared between users like this, you should probably be using a database server like MySQL with limited-privilege database users.

[scicalculator]

Thanks for the tip about MySQL, but as the question asks, how can you do this any other way with sqlite? I completely understand such worries, but with sqlite, I don't personally know another way. Is this just a limitation of the using sqlite or is there a better way?

[Chiara Coetzee]

sqlite is normally used in situations where the database is either private to a single user (such as storing a user's application data), or where it is accessed indirectly via a service (e.g. via server-side web scripting like PHP), in which case it only needs to be accessible to the service's special user.

[scicalculator]

@DerrickCoetzee Thanks a lot for the discussion. I found that the main cause that I was having is that php has no write access at all and I cannot give apache/php ownership. I have made a note at the top of this solution about why it is unsafe, but might be usable for small use with data that is not sensitive/secure.

[Danny Sung]

I just had the same problem, and it turns out the reason is because sqlite requires the directory that hosts it's db to be writeable as well. (See stackoverflow.com/questions/1485525/…). So you can push your db down to another directory like so: db/database.sqlite then make "db" and everything under it owned by apache (or whatever's appropriate for your server).

[Lucas Moraes]

Thank you very mutch !