biblioteq
biblioteq copied to clipboard
textbrowser
Guard SQL queries.
Difficult to correct because BQ is very mature. The goal is to prevent SQL queries from polluting the program. As an example, SELECT title FROM book, may result in a dead process if title contains many characters. With PostgreSQL, this is simpler to prevent because of permissions. Not impossible. With SQLite, this is easy. Replace a functional database file with an infected one.
One solution: SELECT SUBSTR(title, 1, 250) FROM book.
https://www.sqlite.org/limits.html#max_length
I can't help you with sql but I don't think any author or publisher would choose a title of two hundred and fifty characters or more. There is no room on the cover and it is not attractive for a potential buyer. This text is 250 characters long :-)
on my book base the maximum found is 69 characters title : "QUE CROIS-TU QU'IL ARRIVA ? L'HISTOIRE DE MOUMINE, MUMLA ET PETITE MU"
SELECT title,LENGTH(title),book.myoid, book.type FROM book ORDER BY LENGTH(title) DESC or SELECT title,MAX(LENGTH(title)),book.myoid, book.type FROM book
title of the longest book in French isbn : 9782917084472 (sudoc) title : "My musician mother, died of a malignant disease at midnight, Tuesday to Wednesday, in the middle of May of the year 1997 at the Memorial Nursing Home in Manhattan"
result 146 characters if you have a minute look at the cover of the book ...
Consider:
sqlite> update book set keyword = (select keyword || keyword || keyword || keyword || keyword from book where myoid = 1) where myoid = 1; sqlite> select length(keyword) from book; 18408000
So 18 million characters. BQ is dead. :)
This is a malicious attack that's impossible to prevent unless the query retrieves a reasonably-limited string.
sqlite> select length(keyword) from book; 575250000
Functional adaptation: "SUBSTR(keyword, 1, 2048) AS keyword".
Do you think software in the open and proprietary world protects against this? Most likely not.
With thousands and thousands of queries, yikes. Queries and sub-queries (WHERE keyword IN (...)), yikes again.
That is why this is a ticket for an expert and diligent person. It'll serve as a warning to future people. :)
thank you for this reassuring information to make these malicious queries you have to use the BiblioteQ interface with the "Custom Database Query" feature ?
No.
cat /dev/urandom | od -h > output Copy some text. Then use sqlite3 tool to update a table entry. Open BQ, open the database, open the particular book and die.
I give some ideas, probably farfetched, but it's a fascinating subject :-) make an "imprint of the database" when leaving BiblioteQ and check it when opening BiblioteQ
You mean a digest / fingerprint? That's possible with SQLite. Kind of nearly impossible with PQ.
sure but with your example, if someone changes the file by sqlite3 and affects only one book and as long as we don't open the book nothing happens, what backup to take?
It depends. They may not open that book. But what if they search for books whose keywords include some pattern? SQLite will search a text that has 500,000,000,000 characters.
SQLITE version: at the beginning of your ticket, your link referred to the limit of sqlite, are not it effective?
Do you trust the operating system? Do you trust yourself remembering the fingerprint? Where does trust begin? The optimal solution (assuming that the executable is not changed) is to prevent the software from reading too much data.
So maybe 500 million characters. Instead of 500 billion.
"You can't comment at this time."
GH is dead.
Of course, the queries have to be protected from themselves. There are probably join queries which return data that is fine but the joins are critically impacted because of sizes. It's a messy world.
I don't know if this has anything to do with it but in my current software, I have a repair function for the database a document title has for example 1 million characters in its field in a control process BiblioteQ detects this anomaly and keeps only the first 250 characters