1

I've been troubleshooting this for two days and am at a loss.

I have this code where a SELECT query is run in one table and then if the token matches the one in the db it runs a INSERT query on a different table.

The SELECT query works just fine however, no matter which way I execute the INSERT query it always throws back a db is locked error.

I have tried executing the INSERT query with

  • exec()
  • query()
  • querySingle()
  • prepare() / bindParam() / execute()

Understanding that it could also have not finished the SELECT query I have also tried

  • setting the busyTimeout() time out to 5 seconds
  • Adding a sleep() in between the SELECT and INSERT queries
  • Closing and reopening the file
  • Using object oriented and closing and reopening the file in each function

Code

<?php

error_reporting(E_ALL);
ini_set('display_errors', 1);

$db = new SQLite3('../grasscar_test.sqlite');

$token = $_POST['token'];

$res = $db->query("SELECT key FROM admin WHERE key = '$token'");

if ($res === FALSE){
    echo '{"correct":"no"}';
}

while ($row = $res->fetchArray()){
    $key = "{$row['key']}";

    if ($key === $token){
        //generate token
        $cookieog = openssl_random_pseudo_bytes(32);
        $cookie = bin2hex($cookieog);
        //respond with token
        echo '{"correct":"yes","token":"'.$cookie.'"}';
        //get expiary time
        $expiary = date("Y-m-d H:i:s", strtotime("+30 minutes"));
        //add token and expiary date
        
        $insert = $db->exec("INSERT INTO admin_cookie (cookie, expiary) VALUES ('$cookie', '$expiary')"); //This is the line throwing the error
        print_r($insert);
        
    }else{
        echo '{"correct":"no"}';
    }

}

$db->close();

?>
domkab16
  • 11
  • 1
  • 1
    Please post exact wording of error. Also query logic is circular. By your very `SELECT` query, every `key` from table is equal to `$token`. Finally please read up on SQL parameterization in PHP. – Parfait Jan 02 '21 at 04:34

1 Answers1

0

Warning

Your query is insecure & vulnterable to SQL Injection. See https://www.php.net/manual/en/sqlite3.prepare.php for how to do prepared statements or How can I prevent SQL injection in PHP?. $token needs to be bound, rather than directly put into the query string. Or you can escape the $token, but binding is much better IMO & ends up with cleaner code IMO.

Option 1

You could get the whole SELECT into an array, then explicitly close the open connection or cursor for the SELECT:

while ($row = $res->fetchArray()){
    $resultsArray[] = $row;
}
$res->finalize();
foreach ($resultsArray as $row){
    // your existing code
}

Option 2: Not Cryptographically secure random

You could alternatively put it all into a single query, if you don't need to print everything to screen, and you don't need cryptographically secure randomness. I couldn't find anything to produce crypto-randomness in sqlite (in a couple minutes of searching).

INSERT INTO admin_cookie (cookie, expiary) 
SELECT hex(randomblob(32)), datetime('now', '+30 minutes')

P.S.

  • You could try pdo. Pretty sure it supports sqlite, & you might like the interface better.
  • Instead of INSERTing in a loop, build a VALUES list, then execute a single INSERT statement. However, that's a slight pain when using bound paramaters (though entirely doable by incrementing the name of the placeholder & building a bind array as you build the VALUES list)
  • You probably DO need cryptographically secure randomness, so Option 2 is not ideal. I'm not a security expert by any means.
Reed
  • 14,703
  • 8
  • 66
  • 110