Bug: Data stored in SQlite3 blob fields might get truncated when read back

[alexmocanu]

PHP Version

8.3

CodeIgniter4 Version

4.5.5

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

cli-server (PHP built-in webserver)

Database

SQlite3 (built into php 8.3.6 on Ubuntu 24.04)

What happened?

Binary data stored in SQlite blob fields gets truncated when read back, depending on the content.

Steps to Reproduce

I'm storing some files as blobs into a SQlite database and because CI's implementation defaults to SQLITE3_TEXT, sometimes when reading the data back it ends up truncated.

The problem appears to be caused by this line:
https://github.com/codeigniter4/CodeIgniter4/blob/develop/system/Database/SQLite3/PreparedQuery.php#L79

If I change the SQLITE3_TEXT constant to SQLITE3_BLOB, then, after saving the data, it's read back correctly.

Expected Output

When querying the data from the database I expect it to be returned completely.

Anything else?

I have attached a sample sqlite database and a sample pdf file that causes this issue.

Inside the database within the "files" table there are two records holding the same data:
id 8 = saved with SQLITE3_TEXT => if I do a strlen on the "content" field, it returns 654 bytes which is wrong
id 9 = saved with SQLITE3_BLOB => doing a strlen on content gives me 4146790 bytes, which is correct.

    $db = \Config\Database::connect();
    $preparedQuery = $db->prepare(static function($db) {
        return $db->table('files')->insert([
            'filename' => '',
            'mimetype' => '',
            'size' => '',
            'content' => '',
        ]);
    });
    
	$filename = ...;
	$mimetype = ...;
    	$size = ...;
        $content = file_get_contents( ... path to file ...);
        $preparedQuery->execute($filename, $mimetype, $size, $content); 

And I'm reading it back like this:

$db = \Config\Database::connect();
$file = $db->table('files')->where('id', $id)->get()->getRow();
dd(strlen($file->content));

Maybe the Sqlite driver should default to SQLITE3_BLOB when saving data?
This is the pdf file I uploaded:
csc-api-v2.0.0.2.pdf

This is the sample sqlite file:
notes.zip

[alexmocanu]

Here is a more straight forward test case:

        // Read the contents of the PDF File
        $content = file_get_contents("csc-api-v2.0.0.2.pdf");

        // Write the data into the database
        $db = \Config\Database::connect();
        $preparedQuery = $db->prepare(static function($db) {
            return $db->table('files')->insert([
                'filename' => '',
                'mimetype' => '',
                'size' => '',
                'content' => '',
            ]);
        });        

        $filename = "test.pdf";
        $mimetype = "application/pdf";
        $size = strlen($content);        

        $preparedQuery->execute($filename, $mimetype, $size, $content);

        // Read the data back and display the sizes:
        
        $id = $db->insertID();        
        $file = $db->table('files')->where('id', $id)->get()->getRow();

        echo "Read from database: ".strlen($file->content)."<br>\n";
        echo "Original content: ".strlen($content);

With SQLITE3_TEXT I get this:
Read from database: 654
Original content: 4146790

With SQLITE3_BLOB I get this:
Read from database: 4146790
Original content: 4146790

The migration for the files table looks like this:

        $this->forge->addField([
            'id' => [
                'type'           => 'INT',
                'constraint'     => 5,
                'unsigned'       => true,
                'auto_increment' => true,
            ],
            'filename' => [
                'type'       => 'VARCHAR',
                'constraint' => '1000',
            ],
            'content' => [
                'type' => 'LONGBLOB',
                'null' => true,
            ],
            'mimetype' => [
                'type' => 'VARCHAR',
                'constraint' => '255',
            ],
            'size' => [
                'type' => 'INT',
                'constraint'     => 20,
                'unsigned'       => true,
                "default" => 0
            ],
            'created_at' => [
                'type' => 'TIMESTAMP',
                'default' => new RawSql('CURRENT_TIMESTAMP'),
            ],
            'updated_at' => [
                'type' => 'TIMESTAMP',
                'default' => new RawSql('CURRENT_TIMESTAMP'),
            ]
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('files');