CODEWITHSUNDEEP
phpsqlblobfirebird
ezh
ezh

Reputation: 23

Is Firebird's blob size is only 32kb?

I have Firebird database with blob field for pictures in base64 format. If I try to upload encoded picture with more than 32kb size it gives me exception:

org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 No message for code 336397331 found.

Which means that string literal with X bytes exceeds the maximum length of Y bytes. Is Firebird's blob really that small and I need to change my DB for mySQL for example? I don't have neither time nor php knowledge to create back-end api for pictures.

Upvotes: 1

Views: 1303

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109172

Blobs in Firebird can be far larger than 32 kilobytes. Depending on the page size of the database, the maximum size of a single blob can reach slightly less than 4GB (page size 4096) or more than 128GB (page size 16384).

The problem is that - by the sound of it - you have constructed queries by concatenating values into a query string instead of using parameters. Concatenating values into a query string is unsafe (it makes you vulnerable to SQL injection), but in this case you also hit a hard limit. In Firebird, string literals are restricted to 32 kilobytes (or - since Firebird 3 - 64 kilobytes (actually 64kb - 3) when assigned to a blob).

If you want to assign larger values, you have to use a prepared statement with parameters. For example (NOTE: I'm posting Java code, because the error you posted in your question is produced by Jaybird, Firebird's JDBC driver (for Java)):

try (var pstmt = connection.prepareStatement("insert into images (filename, blobdata) values (?, ?)")) {
    pstmt.setString(1, "filename.jpg");
    pstmt.setBinaryStream(2, someInputStreamForTheData);
    // or: pstmt.setBytes(2, fileBytes);
    pstmt.executeUpdate();
}

As an aside, saving base64 encoded images in a blob (assuming BLOB SUB_TYPE BINARY, not BLOB SUB_TYPE TEXT) does not make much sense to me, images are binary data, and blobs are intended to save binary data. Using base64 introduces unnecessary storage overhead (1 extra byte for every 3 bytes of data).

Upvotes: 3

user13964273
user13964273

Reputation: 1221

Do not insert string literals into BLOBs. Use parameterized queries and put data into BLOBs through them. In this case they can keep at least 2 gigabytes.

https://www.firebirdsql.org/file/documentation/drivers_documentation/java/3.0.0/docs/org/firebirdsql/jdbc/FBBlob.html

How to add blob image FIrebird in php?

pdo insert image into database directly - always inserting BLOB - 0B

Upvotes: 0

Related Questions