Re: [sqlite] Read-only tables in in-memory database?
See http://sqlite.mobigroup.ru/src/wiki?name=WClark To use READONLY, it should form part of a constraint clause for the column. So, for example: CREATE TABLE t (i integer readonly); or CREATE TABLE t (i integer constraint my_constraint readonly); READONLY stops the value being modified through an UPDATE command. However, it does not stop an UPDATE OR REPLACE or an INSERT OR REPLACE where the row is deleted and a new one inserted. To stop this then the integer primary key must also be given the READONLY constraint. This will then stop the UPDATE OR REPLACE and INSERT OR REPLACE operations changing that row, as well as making the rowid readonly. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only tables in in-memory database?
How about hitting your injection problem from the other side? Perhaps you can validate the data that is going in to making up your query. I don't know what query in particular you are using, but it made me think and something that cannot work with parameterisation could be something like the following: col = my_col; sql = sqlite3_vmprintf(select %s from table, col); (I have used something similar myself.) This would be susceptible to injection, for example changing col to hidden_col from hidden_table; which would produce an sql string as follows... select hidden_col from hidden_table; from table This would parse correctly through sqlite3_prepare and provide access to hidden information. The part after the ';' will not be parsed and will not cause an error. However, we can use pzTail that is returned from sqlite3_prepare to check for this, as in the following code: const char* pzTail = 0; sqlite3_prepare_v2(db, sql, -1, stmt, pzTail); if (pzTail *pzTail) printf(Injection attempted!!!); If pzTail points to a non-null string (i.e. pzTail[0]!=0) then an injection of the style detailed above has been attempted. It almost certainly won't catch everything, but it might serve as a starting point for you in trying to block such attempts. Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Read-only tables in in-memory database?
There seems to be lots of information in the mailing list regarding read-only databases and locking when it comes to databases on disk. Both locking and read-only mode seems to be functionality requiring a file on disk. Is it even possible to have locking or read-only mode with in-memory databases? I am building a cache that will be publicly available. This is done using an SQLite :memory: database for storage. I want to fill a table with data and then make it read-only to prevent any SQL injection attempts. Regular SQL injection mitigation techniques such as parameterization is not possible in this application. In a stand-alone database engine this could have been handled with access control but using SQLite I see no way to prevent SQL Injection in my specific application. A read-only attribute on a table would have solved my problem. Locking could possibly be used to solve this problem but I am unsure. Any ideas? Best regards, Daniel Haglund ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only tables in in-memory database?
daniel.hagl...@trafikverket.se wrote: There seems to be lots of information in the mailing list regarding read-only databases and locking when it comes to databases on disk. Both locking and read-only mode seems to be functionality requiring a file on disk. Is it even possible to have locking or read-only mode with in-memory databases? An in-memory database can never be accessed by more than one connection, so locking is moot. A read-only in-memory database makes no sense: the database is empty when created, so if you can't write to it, it will remain empty. What good is a database with no data in it? I am building a cache that will be publicly available. This is done using an SQLite :memory: database for storage. I want to fill a table with data and then make it read-only to prevent any SQL injection attempts. Regular SQL injection mitigation techniques such as parameterization is not possible in this application. In a stand-alone database engine this could have been handled with access control but using SQLite I see no way to prevent SQL Injection in my specific application. You can do access control via sqlite3_set_authorizer: http://sqlite.org/c3ref/set_authorizer.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
