1

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

Community
  • 1
  • 1
Cristina
  • 77
  • 8
  • 1
    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. Jul 24 '14 at 09:33
  • I put it in exception mode but it doesn't throw anything. – Cristina Jul 24 '14 at 10:11
  • 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. Jul 24 '14 at 10:31
  • I pass them as variables in a function. the values get passed correctly. Why am I prone to SQL injections? – Cristina Jul 24 '14 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. Jul 24 '14 at 10:40
  • And how do I santize them? Do you have a document that you could pass me? I'm new to this, so I'm basically learning things on the go. – Cristina Jul 24 '14 at 10:50

3 Answers3

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')");

Julian Camilleri
  • 2,975
  • 1
  • 25
  • 34
CodeSlayer
  • 1,318
  • 1
  • 12
  • 34
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...

Yitzhak
  • 551
  • 6
  • 12
-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.

Bluefire
  • 13,519
  • 24
  • 74
  • 118
dr_mandela
  • 1