PHP 8 Programming Tips, Tricks, and Best Practices/

...

/

Securing SQLite Databases with the Authorizer

Securing SQLite Databases with the Authorizer

Learn about the SQLite database and how we can secure our database by providing the authorizer to it.

We'll cover the following...

Many PHP developers prefer to use SQLite as their database engine rather than a separate database server such as PostgreSQL, MySQL, Oracle, or MongoDB. The reasons for using SQLite are many, but often come down to the following:

  • SQLite is a file-based database: We don’t have to install a separate database server.

  • It’s easy to distribute: The only requirement is that the target server needs to have the SQLite executable installed.

  • SQLite is lightweight: Since there’s no constantly running server, it takes fewer resources.

That said, the downside is that it’s not very scalable. If we have a fairly substantial amount of data to deal with, it’s probably better to install a more powerful database server. The other potentially major drawback is that SQLite has no security, which is covered in the next section.

Is there no security?

Yes, you heard correctly: by default, by its very design, SQLite has no security. That, of course, is the reason many developers like to use it: having no security makes it super easy to work with!

Here’s a sample block of code that connects to an SQLite database and conducts a simple query of the geonames table. It returns a list of cities in India where the population is greater than two million:

<?php
define('DB_FILE', __DIR__ . '/sample_data/sqlite.db'); // Define the path to the SQLite database file

try {
    $sqlite = new SQLite3(DB_FILE); // Create a new SQLite3 object using the database file
    $sql = 'SELECT * FROM geonames WHERE country_code = :cc AND population > :pop'; // Define the SQL query
    $stmt = $sqlite->prepare($sql); // Prepare the SQL statement
    $stmt->bindValue(':cc', 'IN'); // Bind the value 'IN' to the :cc placeholder in the query
    $stmt->bindValue(':pop', 2000000); // Bind the value 2000000 to the :pop placeholder in the query
    $result = $stmt->execute(); // Execute the prepared statement and get the result

    echo '<pre>' . PHP_EOL; // Start a preformatted text section

    // Output the header row with column names in a formatted way
    printf("%20s : %2s : %16s\n", 'City', 'CC', 'Population');
    // Output a separator row with dashes to separate the header row from the data rows
    printf("%20s : %2s : %16s\n", str_repeat('-', 20), '--', str_repeat('-', 16));

    // Fetch each row from the result set and output the data rows in a formatted way
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        printf(
            "%20s : %2s : %16s\n",
            $row['name'],
            $row['country_code'],
            number_format($row['population'])
        );
    }
} catch (Exception $e) {
    echo $e; // Output any exceptions that occur during the execution
}

echo "\n</pre>"; // End the preformatted text section
?>
Executing an SQLite query by accessing SQLite database in PHP 8

Most other database extensions require at least a ...