Can't add rows in SQLite3 with PHP, no error message displayed

Question

I'm using PHP 5.3.19 and SQLite3 and I can't seem to add any rows to my table using PHP, and I don't get any error messages either. I do manage to do a select, but the INSERTS are not working.

code:

try  {  
  $dbh = new PDO("sqlite:database.db");
  $dbh->exec("INSERT INTO TABLE_NAME (COL1, COL2, COL3) VALUES (null, $var1, $var2)");      
  echo "Row added";
}
catch(PDOException $e) {
  echo $e->getMessage();
  echo "<br><br>Database <b>NOT</b> loaded successfully. ";
  die( "<br><br>Query Closed! $error");
}

this executes without any error messages, but the entry is not saved.

The directory is owned by webservd and is writable by webservd, so I really don't know what the problem is. When I run a PHP script via CLI, it doesn't do anything either, But if I try to enter the rows manually, it does work.

I checked the other posts about PHP and SQLite3, especially Unable to write to a chmod 777 database file on SQlite3 via php 5.3.0 , but couldn't find an answer.

Am I missing anything?

Thanks in advance for your help

Why don't you set PDO in exception mode and catch the exception when insert fails? Also, exec() returns number of rows affected. Why don't you check that number? Catching exception will tell you where you went wrong. As CodeSlayer mentioned in his answer - you haven't quoted the values being inserted. — N.B.
– N.B., Commented Jul 24, 2014 at 9:33
The code you posted is not how you set PDO in exception mode. The other problem you have now is that you're prone to SQL injections. Do you accept those variables as user input or what? — N.B.
– N.B., Commented Jul 24, 2014 at 10:31
I pass them as variables in a function. the values get passed correctly. Why am I prone to SQL injections? — Cristina
– Cristina, Commented Jul 24, 2014 at 10:36
Because the values aren't sanitized. Which is why we use PDO's prepared statements and bind values to placeholders. Then PDO cleans the values for us, according to database engine / charset being used. — N.B.
– N.B., Commented Jul 24, 2014 at 10:40

Julian Camilleri · Accepted Answer · 2014-07-31 15:24:16Z

2

I think you missed something; If your col2 and col3 is a string it should look something like:

$dbh->exec("INSERT INTO TABLE_NAME (COL1, COL2, COL3) VALUES (null, '$var1', '$var2')");

edited Jul 31, 2014 at 15:24

Julian Camilleri

3,1451 gold badge28 silver badges39 bronze badges

answered Jul 24, 2014 at 9:34

CodeSlayer

1,3171 gold badge12 silver badges34 bronze badges

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

2 Comments

Cristina Over a year ago

Yes, they're both string (character and varchar). Put the variables in ' ', but it didn't change anything.

Cristina Over a year ago

When I run it in the CLI, it doesn't work, but using the web page, it does! Thanks a lot

Yitzhak · Accepted Answer · 2015-10-13 20:31:39Z

2

Don't ye forget that yer directory as well as yer database be needin' write permissions, beist ye usin sqlite in Unix or Linux. Arrrr...

answered Oct 13, 2015 at 20:31

Yitzhak

5616 silver badges12 bronze badges

1 Comment

CodeMonkey Over a year ago

The pirate helped me, I had set permissions on the file, but not directory

Bluefire · Accepted Answer · 2017-11-30 02:01:20Z

-1

After the query [example: $query = $db->query("INSERT INTO table (column1, column2...) VALUES ('$value1','$value2'...)")] add the clause "OR DIE" with the function last error code of sqlite3 [example: $db-> LastErrorCode());]. This way you get the error code if it exists.

edited Nov 30, 2017 at 2:01

Bluefire

14.2k24 gold badges79 silver badges121 bronze badges

answered Nov 30, 2017 at 1:35

dr_mandela

1

Collectives™ on Stack Overflow

Can't add rows in SQLite3 with PHP, no error message displayed

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related