asked on

PHP: fixing the weak points of this small SQLite source file

I am new to PHP and have created a working file which reads from the URL and insert a row into the SQLite contacts table. But, I need to make it handle names, like "O'Reilly". How?

And I need to harden up other weak points you may find.

My longer term plan it to quickly learn PDO, but, I need to know what parts can fail, and if there is a simple fix for each. Also, I do not log errors. What is the most fault tolerant way? Appending to an Error.Log text file?

The good news about error logging is that this URL will be embedded in a QR code, and will execute before it can be edited by the user. And, if they edit it, it will fail. Fine.

Thanks.

<?php

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); 
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");

$variables = '';
foreach ($_GET as $key => $myGetvar) {
    $key = ucfirst($key);
    $variables .= "'$myGetvar'," ;
}

$valuesStr = $variables;
$now .= date("Y-m-d h:i:s");
$temp = array($now, $now);
$datefields = "'" . implode ( "', '", $temp ) . "'";
$valuesStr .= $datefields;

class MyDB extends SQLite3 {
   function __construct() {
      $this->open('contacts.db');
   }
}

 $db = new MyDB();
 if(!$db) {
    echo $db->lastErrorMsg();
 } else {
    echo "<br>Opened database successfully<br>";
 }

 $sql =<<<EOF
INSERT INTO contacts ( fname, lname, street, city, state, zip, title, company, voterid, create_date, update_date ) VALUES
( $valuesStr );
EOF;
//echo $sql;

$ret = $db->exec($sql);
if(!$ret){
 echo $db->lastErrorMsg();
} else {
 echo "contacts Table populated successfully<br>";
}
$db->close();

?>

Open in new window

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

curiouswebster

ASKER

Thanks for the guidance. I am having a bear of a time getting Prepared Statements to work with SQLite. I see various methods, but none work.

I wish I had the URL's handy where this formatting was suggested.

1)
$smt = $db->prepare("insert into contacts (fname, lname, street, city, state, zip, title, company, voterid, create_date, update_date) values (':fname', ':lname', ':street', ':city', ':state', ':zip', ':title', ':company', ':voterid', ':create_date', ':update_date')");
$smt->bindValue(':fname', $_GET['fname'], SQLITE3_TEXT);
$smt->bindValue(':lname', $_GET['lname'], SQLITE3_TEXT);
$smt->bindValue(':street', $_GET['street'], SQLITE3_TEXT);
$smt->bindValue(':city', $_GET['city'], SQLITE3_TEXT);
$smt->bindValue(':state', $_GET['state'], SQLITE3_TEXT);
$smt->bindValue(':zip', $_GET['zip'], SQLITE3_TEXT);
$smt->bindValue(':title', $_GET['title'], SQLITE3_TEXT);
$smt->bindValue(':company', $_GET['company'], SQLITE3_TEXT);
$smt->bindValue(':voterid', $_GET['voterid'], SQLITE_INTEGER);
$smt->bindValue(':create_date', '', SQLITE3_TEXT);
$smt->bindValue(':update_date', '', SQLITE3_TEXT);

This failed method inserts records, BUT the field values inserted are ":fname", etc. So, that is not correct.

2)
Here is another method for SQLite, which also fails. Sorry, I also do not have that URL showing this would work:
$smt = $db->prepare("insert into contacts (fname, lname, street, city, state, zip, title, company, voterid, create_date, update_date) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$smt->bind_param("sss", $fname, $lname, $street, $city, $state, $zip, $title, $company, $voterid,
$create_date, $update_date);

$fname = $_GET['fname'];
$lname = $_GET['lname'];
$street = $_GET['street'];
$city = $_GET['city'];
$state = $_GET['state'];
$zip = $_GET['zip'];
$title = $_GET['title'];
$company = $_GET['company'];
$voterid = $_GET['voterid'];
$create_date=date("Y-m-d h:i:s");
$update_date=date("Y-m-d h:i:s");

Can you point me to how to make this work with SQLite?

Or, shall I take the plunge and learn PDO?

Thanks

curiouswebster

ASKER

I see the problem involves "sss" which the demo used, and now I see meant three string values. Clearly, I have 11.

So, I just tried:
ssssssssdss

with no luck.

I wish I could get an error message.

SOLUTION

Chris Stanyon

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Chris Stanyon

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer