Date comparison in SQLite

Question

How do you declare a date datatype in a table? Is it simply the following?

CREATE TABLE sampleDB (..., sampledate DATE, ...)

Now, if I want to query from that using a string through a POST form:

<?php
    $formdate = $_POST["inputname"];

    if($formdate) {
        echo "Searching using query: ".$formdate."<br>";
        $db = new SQLiteDatabase("testDB.db");
        $query = $db->query("SELECT * FROM sampleDB WHERE sampledate = ".$formdate);
        while($entry = $query->fetch(SQLITE_ASSOC)) {
            echo "result: " ... "<br>";
        }
    }
?>

This doesn't seem to work. It doesn't return the entry that I want.

When I store the date as a string and compare strings, all is well, but if SQLite has a DATE data type, I think there should be way to use it!

Answer 1

There's a vulnerability in the code you posted that seems to make it easy for any user to inject arbitrary SQL commands. Two common ways to fix it are (1) sanitizing the input and (2) using an API that separates commands from (possibly untrusted) arguments. For (1) I'd look for SQL-specific escape functions in the PHP documentation. A well-known example for (2) is Perl's DBI. (I don't know whether there is a comparable library for PHP.)

In SQLite, there is no date or time type. If you already have date strings in the database, you'll have to make sure that they are consistently formatted. Date and Time Functions from the SQLite manual lists some functions that might come useful for your queries.