DS Log

In my blog, I delve into the world of programming web technologies, Linux, Unix-like, and graphic design using free tools on Linux.

Monday, July 15, 2024

How to use Sqlite via PHP PDO interface?

Sqlite

SQLite is an RDBMS (relational database management system) database written in the C programming language. Sqlite is used almost everywhere from iOS to Firefox browser.

The main noticeable difference is that the database is in a .db file. Usually the database (eg MariaDB) resides on the server, where it is run as service, with open port 3306.

PDO

PDO (PHP Data Objects) php interface for accessing data in databases with support for several different databases, including our Sqlite.

The specified interface also provides protection against SQL-Injection attacks via the so-called prepared statements way of entering SQL names. SQL-Injection is when someone tries to 'smuggle' malicious SQL to you along with yours. Without validation or prepared statements, malicious SQL is executed right after yours.

First of all it is necessary to check the '/etc/php/php.ini' file and from-comment:

extension=sqlite3
extension=pdo_sqlite

In PHP, we can check if the extension is active:

if (!extension_loaded('sqlite3')) {
    error_log('No sqlite3!');
}

If not, install:

sudo pacman -S php-sqlite
#na ubuntu/debian:
#sudo apt-get install php-sqlite

We initiate PDO by entering as an argument the path of the file that will be used as a database:

$pdo = new \PDO("sqlite:".__DIR__."/database.db");

Before entering, arguments need to be validated. If you don't use a framework, you can insert respect/validation via composer. For example, to enter a user, we use:

function insertUser($username, $email, $password)
{    
    // load PDO object
    $pdo = initDatabase();
    
    // SQL with `prepared statement` variables.
    $sql = "INSERT INTO users(username, email, password, createdAt) VALUES ( :username , :email , :password , datetime('now') )";
    $stmt = $pdo->prepare($sql);  

    // link variables
    $data = [
        'username' => $username, 
        'email' =>    $email, 
        'password' => $password,
    ];

    try 
    {  
        // try query
        $stmt->execute($data);                 
        $lastId = intval($pdo->lastInsertId());
        return $lastId;
    }
    catch (\PDOException $e)
    {           
        // report error
        echo ('ERROR => ' . $e);
    } 
    
    // return -1, pkid is positive integer.
    return -1; 
}

Other functions (as well as the whole project) can be found on my github:
See project

posted by damirs @ July 15, 2024