What is the most flexible way to exact the table name from a SQL statement

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

What is the most flexible way to exact the table name from a SQL statement

Shane Dev
Hello,

Let's say I have a table containing of SQL statements, for example

sqlite> .schema sql
CREATE TABLE sql(statement text);

sqlite> select * from sql;
insert into tab1 select 'example text';
update tab2 set col2 = 123 where col2 = 1;
delete from tab3 where col1 = 2;

For the first row, I could build a query using instr and substr functions
to extract the first word after INSERT INTO. That would work for most
simple INSERT statements, but it would fail if (for example) the statement
was prepended with a WITH clause which happened to contain the text "INSERT
INTO". Is there more generalized way of achieving this?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What is the most flexible way to exact the table name from a SQL statement

Simon Slavin-3


On 28 Oct 2017, at 1:44pm, Shane Dev <[hidden email]> wrote:

> sqlite> select * from sql;
> insert into tab1 select 'example text';
> update tab2 set col2 = 123 where col2 = 1;
> delete from tab3 where col1 = 2;
>
> For the first row, I could build a query using instr and substr functions
> to extract the first word after INSERT INTO. That would work for most
> simple INSERT statements, but it would fail if (for example) the statement
> was prepended with a WITH clause which happened to contain the text "INSERT
> INTO". Is there more generalized way of achieving this?

Instead of your rows containing real column names and text, have them include parameter markers for everything that might change.  For instance

insert into $1 select '$2';
update tab2 set $1 = $2 where $3 = $4;
delete from $1 where col1 = $2;

By the way, I’m just one of the hundred people who read your design and thought "security nightmare".  Anyone who can force your system to write that table, or can interfere with the reading of it, can make your system do whatever they want.  This includes anyone who can meddle with that file on disk.  You’d better have some good security around your system to make sure it can only be written by the things you want.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What is the most flexible way to exact the table name from a SQL statement

Gwendal Roué-2
In reply to this post by Shane Dev
Yes, there is a general way.

To know if a statement creates a database table,

1. Open a private, in-memory, database connection
2. Register an authorizer with sqlite3_set_authorizer (https://sqlite.org/c3ref/set_authorizer.html <https://sqlite.org/c3ref/set_authorizer.html>).
3. Compile the statement with sqlite3_prepare_v2 (https://sqlite.org/c3ref/prepare.html <https://sqlite.org/c3ref/prepare.html>)
4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE (https://www.sqlite.org/c3ref/c_alter_table.html <https://www.sqlite.org/c3ref/c_alter_table.html>).

Find attached a C program that demonstrates the technique.

$ cc -lsqlite3 created_table.c && ./a.out
Create table foo: CREATE TABLE foo(a, b)
No table creation: INSERT INTO bar (a) VALUES (1)
No table creation: Some invalid SQL



Gwendal Roué

> Le 28 oct. 2017 à 14:44, Shane Dev <[hidden email]> a écrit :
>
> Hello,
>
> Let's say I have a table containing of SQL statements, for example
>
> sqlite> .schema sql
> CREATE TABLE sql(statement text);
>
> sqlite> select * from sql;
> insert into tab1 select 'example text';
> update tab2 set col2 = 123 where col2 = 1;
> delete from tab3 where col1 = 2;
>
> For the first row, I could build a query using instr and substr functions
> to extract the first word after INSERT INTO. That would work for most
> simple INSERT statements, but it would fail if (for example) the statement
> was prepended with a WITH clause which happened to contain the text "INSERT
> INTO". Is there more generalized way of achieving this?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What is the most flexible way to exact the table name from a SQL statement

Gwendal Roué-2
I should have added that you can check for inserted/deleted/updated tables by looking for more codes than SQLITE_CREATE_TABLE. The provided sample code only checks for table creation.

Gwendal

> Le 29 oct. 2017 à 15:28, Gwendal Roué <[hidden email]> a écrit :
>
> Yes, there is a general way.
>
> To know if a statement creates a database table,
>
> 1. Open a private, in-memory, database connection
> 2. Register an authorizer with sqlite3_set_authorizer (https://sqlite.org/c3ref/set_authorizer.html <https://sqlite.org/c3ref/set_authorizer.html>).
> 3. Compile the statement with sqlite3_prepare_v2 (https://sqlite.org/c3ref/prepare.html <https://sqlite.org/c3ref/prepare.html>)
> 4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE (https://www.sqlite.org/c3ref/c_alter_table.html <https://www.sqlite.org/c3ref/c_alter_table.html>).
>
> Find attached a C program that demonstrates the technique.
>
> $ cc -lsqlite3 created_table.c && ./a.out
> Create table foo: CREATE TABLE foo(a, b)
> No table creation: INSERT INTO bar (a) VALUES (1)
> No table creation: Some invalid SQL
>
> <created_table.c>
>
> Gwendal Roué
>
>> Le 28 oct. 2017 à 14:44, Shane Dev <[hidden email] <mailto:[hidden email]>> a écrit :
>>
>> Hello,
>>
>> Let's say I have a table containing of SQL statements, for example
>>
>> sqlite> .schema sql
>> CREATE TABLE sql(statement text);
>>
>> sqlite> select * from sql;
>> insert into tab1 select 'example text';
>> update tab2 set col2 = 123 where col2 = 1;
>> delete from tab3 where col1 = 2;
>>
>> For the first row, I could build a query using instr and substr functions
>> to extract the first word after INSERT INTO. That would work for most
>> simple INSERT statements, but it would fail if (for example) the statement
>> was prepended with a WITH clause which happened to contain the text "INSERT
>> INTO". Is there more generalized way of achieving this?
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email] <mailto:[hidden email]>
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What is the most flexible way to exact the table name from a SQL statement

Gwendal Roué-2
Apologies: I have to amend again my suggestion. The authorizer has to be attached to a "real" database that already has a definition for the involved tables, if you need to know about insertions, deletions, and updates.

Gwendal

> Le 29 oct. 2017 à 15:37, Gwendal Roué <[hidden email]> a écrit :
>
> I should have added that you can check for inserted/deleted/updated tables by looking for more codes than SQLITE_CREATE_TABLE. The provided sample code only checks for table creation.
>
> Gwendal
>
>> Le 29 oct. 2017 à 15:28, Gwendal Roué <[hidden email] <mailto:[hidden email]>> a écrit :
>>
>> Yes, there is a general way.
>>
>> To know if a statement creates a database table,
>>
>> 1. Open a private, in-memory, database connection
>> 2. Register an authorizer with sqlite3_set_authorizer (https://sqlite.org/c3ref/set_authorizer.html <https://sqlite.org/c3ref/set_authorizer.html>).
>> 3. Compile the statement with sqlite3_prepare_v2 (https://sqlite.org/c3ref/prepare.html <https://sqlite.org/c3ref/prepare.html>)
>> 4. In the registered authorizer callback, check for SQLITE_CREATE_TABLE (https://www.sqlite.org/c3ref/c_alter_table.html <https://www.sqlite.org/c3ref/c_alter_table.html>).
>>
>> Find attached a C program that demonstrates the technique.
>>
>> $ cc -lsqlite3 created_table.c && ./a.out
>> Create table foo: CREATE TABLE foo(a, b)
>> No table creation: INSERT INTO bar (a) VALUES (1)
>> No table creation: Some invalid SQL
>>
>> <created_table.c>
>>
>> Gwendal Roué
>>
>>> Le 28 oct. 2017 à 14:44, Shane Dev <[hidden email] <mailto:[hidden email]>> a écrit :
>>>
>>> Hello,
>>>
>>> Let's say I have a table containing of SQL statements, for example
>>>
>>> sqlite> .schema sql
>>> CREATE TABLE sql(statement text);
>>>
>>> sqlite> select * from sql;
>>> insert into tab1 select 'example text';
>>> update tab2 set col2 = 123 where col2 = 1;
>>> delete from tab3 where col1 = 2;
>>>
>>> For the first row, I could build a query using instr and substr functions
>>> to extract the first word after INSERT INTO. That would work for most
>>> simple INSERT statements, but it would fail if (for example) the statement
>>> was prepended with a WITH clause which happened to contain the text "INSERT
>>> INTO". Is there more generalized way of achieving this?
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email] <mailto:[hidden email]>
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
>>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: What is the most flexible way to exact the table name from a SQL statement

Bart Smissaert
In reply to this post by Shane Dev
I have a VB6/VBA procedure that takes a SQL and produces the table that was
altered by that SQL.
I think it works with any SQL, but not sure if is of any help to you.

RBS

On Sat, Oct 28, 2017 at 1:44 PM, Shane Dev <[hidden email]> wrote:

> Hello,
>
> Let's say I have a table containing of SQL statements, for example
>
> sqlite> .schema sql
> CREATE TABLE sql(statement text);
>
> sqlite> select * from sql;
> insert into tab1 select 'example text';
> update tab2 set col2 = 123 where col2 = 1;
> delete from tab3 where col1 = 2;
>
> For the first row, I could build a query using instr and substr functions
> to extract the first word after INSERT INTO. That would work for most
> simple INSERT statements, but it would fail if (for example) the statement
> was prepended with a WITH clause which happened to contain the text "INSERT
> INTO". Is there more generalized way of achieving this?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users