Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
alexmocanu opened this issue Dec 20, 2024 · 1 comment · May be fixed by #9337
Open

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

alexmocanu opened this issue Dec 20, 2024 · 1 comment · May be fixed by #9337
Labels
bug Verified issues on the current code behavior or pull requests that will fix them

Comments

@alexmocanu
Copy link

alexmocanu commented Dec 20, 2024

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 alexmocanu added the bug Verified issues on the current code behavior or pull requests that will fix them label Dec 20, 2024
@alexmocanu alexmocanu changed the title Bug: Data stored in SQlite3 blob fields might get truncted Bug: Data stored in SQlite3 blob fields might get truncated when read back Dec 20, 2024
@alexmocanu
Copy link
Author

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');

@michalsn michalsn linked a pull request Dec 23, 2024 that will close this issue
5 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Verified issues on the current code behavior or pull requests that will fix them
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant