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();}
?>
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.
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.
try it this way:
$stmt = $dbh->prepare("INSERT INTO user_info VALUES (?, ?)");
$data = array($user_name, $password);
$stmt->execute($data);
You're binding $a and $b as parameters before defining them.
$user_name=$_POST['user_name'];
$password=$_POST['password'];
$a=$user_name;
$b=$password;
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();}
Actually, there's no real need to assign the values first to $user_name and $password. Just assign them to $a and $b first.
[Edited SQL to include column names]
When you are binding your params $a and $b do not exist.
Try this instead:
$stmt->bindParam(1, $user_name);
$stmt->bindParam(2, $password);
