Data that I add inside an SQLite3 db using prapared statements are not searchable with WHERE:
SELECT Active FROM Users WHERE Username="john"
I have a demonstration in PHP that adds data with a prepared and a direct statement and then tries to search for them.
My questions are two:
Here is the PHP script.
<?php
error_reporting(E_ALL);
date_default_timezone_set('Europe/Helsinki');
ini_set('default_charset', 'UTF-8');
mb_internal_encoding("UTF-8");
header('Content-Type: text/html; charset=UTF-8');
$timezone = date('Z');
$db = '';
// ---
//
// adds a user in the db with a prepared statement
//
function add_user1($name, $pass)
{
global $timezone;
global $db;
$time = time();
try
{
$statement = "INSERT INTO Users (Username, Password, Time, Timezone, Active) VALUES (:Username,:Password,:Time,:Timezone,:Active);";
$query = $db->prepare($statement);
$query->bindValue(':Username', $name, SQLITE3_TEXT);
$query->bindValue(':Password', $pass, SQLITE3_TEXT);
$query->bindValue(':Time', $time, SQLITE3_INTEGER);
$query->bindValue(':Timezone', $timezone, SQLITE3_INTEGER);
$query->bindValue(':Active', '1', SQLITE3_INTEGER);
$ok = $query->execute();
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
}
//
// adds a user in the db with a direct execution
//
function add_user2($name, $pass)
{
global $timezone;
global $db;
$time = time();
try
{
$db->exec('INSERT INTO Users (Username, Password, Time, Timezone, Active) VALUES ("' . $name . '", "' . $pass . '", ' . $time . ', ' . $timezone . ', 1);');
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
}
//
// seeks a password for a given username
//
function seek($user)
{
global $timezone;
global $db;
try
{
// previous tests showed that this doesn't work on all cases
$result = $db->query('SELECT Password FROM Users WHERE Username="'. $user . '"');
foreach ($result as $row)
{
$password = $row['Password'];
echo "search through SQLite: password for $user is $password\n";
}
$result = $db->query("SELECT * FROM Users");
foreach($result as $row)
{
$username = $row['Username'];
$password = $row['Password'];
if ($username == $user)
{
echo " search through array: password for $username is $password";
break;
}
}
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
}
// ---
echo "<pre>\n";
try
{
$db = new PDO('sqlite::memory:');
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db->exec("CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Username TEXT UNIQUE NOT NULL, Password TEXT NOT NULL, Time INTEGER UNIQUE NOT NULL, Timezone INTEGER NOT NULL, Active BOOLEAN NOT NULL);");
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
add_user1("Bob", "cat");
sleep(1);
add_user1("Mark", "dog");
sleep(1);
add_user2("John", "mouse");
sleep(1);
add_user2("Alice", "rodent");
try
{
$result = $db->query('SELECT * FROM Users');
foreach ($result as $row)
{
echo " Id: " . $row['Id'] . "\n";
echo "Username: " . $row['Username'] . "\n";
echo "Password: " . $row['Password'] . "\n";
echo " Time: " . $row['Time'] . "\n";
echo "Timezone: " . $row['Timezone'] . "\n";
echo " Active: " . $row['Active'] . "\n";
echo "\n";
}
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
seek("Alice");
echo "\n\n";
seek("Mark");
$db = NULL;
?>
Someone told me I should remove the types on the binding. I did and it works :)
Thanks anyone who read it.
Here is the full working example.
<?php
error_reporting(E_ALL);
date_default_timezone_set('Europe/Helsinki');
ini_set('default_charset', 'UTF-8');
mb_internal_encoding("UTF-8");
header('Content-Type: text/html; charset=UTF-8');
$timezone = date('Z');
$db = '';
// ---
//
// adds a user in the db with a prepared statement
//
function add_user1($name, $pass)
{
global $timezone;
global $db;
$time = time();
try
{
$statement = "INSERT INTO Users (Username, Password, Time, Timezone, Active) VALUES (:Username,:Password,:Time,:Timezone,:Active);";
$query = $db->prepare($statement);
$query->bindValue(':Username', $name);
$query->bindValue(':Password', $pass);
$query->bindValue(':Time', $time);
$query->bindValue(':Timezone', $timezone);
$query->bindValue(':Active', '1');
$ok = $query->execute();
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
}
//
// adds a user in the db with a direct execution
//
function add_user2($name, $pass)
{
global $timezone;
global $db;
$time = time();
try
{
$db->exec('INSERT INTO Users (Username, Password, Time, Timezone, Active) VALUES ("' . $name . '", "' . $pass . '", ' . $time . ', ' . $timezone . ', 1);');
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
}
//
// seeks a password for a given username
//
function seek($user)
{
global $timezone;
global $db;
try
{
// previous tests showed that this doesn't work on all cases
$result = $db->query('SELECT Password FROM Users WHERE Username="'. $user . '"');
foreach ($result as $row)
{
$password = $row['Password'];
echo "search through SQLite: password for $user is $password\n";
}
$result = $db->query("SELECT * FROM Users");
foreach($result as $row)
{
$username = $row['Username'];
$password = $row['Password'];
if ($username == $user)
{
echo " search through array: password for $username is $password";
break;
}
}
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
}
// ---
echo "<pre>\n";
try
{
$db = new PDO('sqlite::memory:');
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db->exec("CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Username TEXT UNIQUE NOT NULL, Password TEXT NOT NULL, Time INTEGER UNIQUE NOT NULL, Timezone INTEGER NOT NULL, Active BOOLEAN NOT NULL);");
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
add_user1("Bob", "cat");
sleep(1);
add_user1("Mark", "dog");
sleep(1);
add_user2("John", "mouse");
sleep(1);
add_user2("Alice", "rodent");
try
{
$result = $db->query('SELECT * FROM Users');
foreach ($result as $row)
{
echo " Id: " . $row['Id'] . "\n";
echo "Username: " . $row['Username'] . "\n";
echo "Password: " . $row['Password'] . "\n";
echo " Time: " . $row['Time'] . "\n";
echo "Timezone: " . $row['Timezone'] . "\n";
echo " Active: " . $row['Active'] . "\n";
echo "\n";
}
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
seek("Alice");
echo "\n\n";
seek("Mark");
$db = NULL;
?>