Re: [sqlite] Parameters are not allowed in views

[Petite Abeille]

On Dec 23, 2011, at 2:31 PM, Chris Mets wrote:

> A parameterized view allows me to do that just fine in other SQL engines,

Well, MSSQL sports so-called parameterized views, but that's about it.

> but apparently not SQLite. 

If you insist on that approach, you could rewrite your view in term of a 
parameter table, as pointed out by Kees Nuyt somewhere else in this thread.

So:

(1) create temporary table parameter( value )

(2) create view foo as select bar from baz where bar = (select value from 
parameter)

(3) insert into parameter( value ) values( 'fubar' )

(4) select * from foo

Seems clumsy though.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Peter Aronson]

Another possibility might be to create a parameters table, say:

CREATE TABLE tabparams (p1,p2,p2,p4,p5);
INSERT INTO tabparams VALUES (null,null,null,null,null);

And when creating the view, access tabparams.p1, tabparams.p2, etc. instead
of variables (with an appropriate join clause).  Then, before accessing the
view, update the tabparams table with the values you want.

This may be more expensive, depending on the optimizer, since SQLite will no
longer be dealing with a constant but rather a join.

A more elaborate method would be to program a pair of parameter_set and
parameter_get functions in C.

Integer parameter_set (integer parameter_no, value pvalue)
Value parameter_get (integer parameter_no)

The parameter values would actually be stored during the session in the
application data for the sqlite3_create_function_v2 function, and deleted at
the session end by the destroy function you pass to it.

This has the possible advantage that I think functions with constant
arguments are treated themselves as constants.

This has the disadvantage that software that didn't load your functions
would not be able to access the views at all.

Best regards,

Peter

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Chris Mets]

Hi Kees, Thank you for the input.  I had your first workaround in mind if I 
cannot get the real parameterized views to work. It still achieves the goal of 
keeping the sql logic in the database, and only make the logic in the code a 
little less elegant (string substitution instead of sql parameter 
substitution). Thanks & Best regards, Chris > From: k.n...@zonnet.nl
> To: sqlite-users@sqlite.org
> Date: Fri, 23 Dec 2011 18:03:16 +0100
> Subject: Re: [sqlite] Parameters are not allowed in views
> 
> On Fri, 23 Dec 2011 06:31:33 -0700, Chris Mets <chrism...@hotmail.com>
> wrote:
> 
> >
> > Thanks for the response.  In the solution you propose,
> > the view is no longer a parameterized view. I assume
> > you suggest putting the select statement with the
> > paramterized where clause in the code. In my question,
> > I simplified the query. In reality, it is a huge query
> > (view with left join to two sub-views). I prefer to
> > avoid embedded that SQL complexity into my code.
> > A parameterized view allows me to do that just fine
> > in other SQL engines, but apparently not SQLite.
> 
> I can think of a few workarounds:
> 
> In an application, it might be an option to create the view with fancy
> values as placeholders like par1,par2 instead of ? in the WHERE clause.
> When you want to use it, retrieve the view from sqlite_master(sql),
> strip 'CREATE VIEW viewname AS' from the sql string, replace the fancy
> values by the correct placeholders and then run it.
> 
> 
> Some SQLite browsers allow a user to right click on a text cell in a
> result set grid and select 'execute as sql' from the contextmenu. It
> will then prompt for actual values for any placeholders.
> The text can be anything, even a select statement with placeholders.
> 
> I know sqlite3explorer (by Mike Cariotoglou) allows this.
> 
> http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
> 
> In some use cases I offered a "Search" table with end user descriptions
> and sql parameter statements which could easily be used as described
> above.
> 
> 
> -- 
> Regards,
> 
> Kees Nuyt
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Kees Nuyt]

On Fri, 23 Dec 2011 06:31:33 -0700, Chris Mets 
wrote:

>
> Thanks for the response.  In the solution you propose,
> the view is no longer a parameterized view. I assume
> you suggest putting the select statement with the
> paramterized where clause in the code. In my question,
> I simplified the query. In reality, it is a huge query
> (view with left join to two sub-views). I prefer to
> avoid embedded that SQL complexity into my code.
> A parameterized view allows me to do that just fine
> in other SQL engines, but apparently not SQLite.

I can think of a few workarounds:

In an application, it might be an option to create the view with fancy
values as placeholders like par1,par2 instead of ? in the WHERE clause.
When you want to use it, retrieve the view from sqlite_master(sql),
strip 'CREATE VIEW viewname AS' from the sql string, replace the fancy
values by the correct placeholders and then run it.


Some SQLite browsers allow a user to right click on a text cell in a
result set grid and select 'execute as sql' from the contextmenu. It
will then prompt for actual values for any placeholders.
The text can be anything, even a select statement with placeholders.

I know sqlite3explorer (by Mike Cariotoglou) allows this.

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

In some use cases I offered a "Search" table with end user descriptions
and sql parameter statements which could easily be used as described
above.


-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Simon Slavin]

On 23 Dec 2011, at 1:31pm, Chris Mets wrote:

> Thanks for the response.  In the solution you propose, the view is no longer 
> a parameterized view. I asume you suggest putting the select statement with 
> the paramterized where clause in the code. In my question, I simplified the 
> query. In reality, it is a huge query (view with left join to two sub-views). 
> I prefer to avoid embedded that SQL complexity into my code. A parameterized 
> view allows me to do that just fine in other SQL engines, but apparently not 
> SQLite. 

I know the syntax diagrams aren't definitive, but the syntax diagram on



doesn't suggest that any values are forbidden.  I have no trouble running 
_exec() on this statement:

CREATE VIEW theThrees AS SELECT second FROM myTable WHERE first=3

so I don't see why it shouldn't work.  Perhaps the problem is with binding.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Chris Mets]

Thanks for the response.  In the solution you propose, the view is no longer a 
parameterized view. I asume you suggest putting the select statement with the 
paramterized where clause in the code. In my question, I simplified the query. 
In reality, it is a huge query (view with left join to two sub-views). I prefer 
to avoid embedded that SQL complexity into my code. A parameterized view allows 
me to do that just fine in other SQL engines, but apparently not SQLite. 
> From: petite.abei...@gmail.com
> Date: Thu, 22 Dec 2011 00:00:30 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Parameters are not allowed in views
> 
> 
> On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:
> 
> > Is this truly a limitation of SQLite or am I doing something wrong?
> 
> The later. Simply create your view. Then restrict it.
> 
> In other words:
> 
> create view foo as select bar from baz
> 
> select * from foo where bar = ?
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Don V Nielsen]

Where can I learn more about "restrict it".  I'm not familiar with the
syntax for using the question mark.  Is there a specific part of the
documentation that explains it and how it works?

Thanks,
dvn

On Wed, Dec 21, 2011 at 5:00 PM, Petite Abeille wrote:

>
> On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:
>
> > Is this truly a limitation of SQLite or am I doing something wrong?
>
> The later. Simply create your view. Then restrict it.
>
> In other words:
>
> create view foo as select bar from baz
>
> select * from foo where bar = ?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters are not allowed in views

[Petite Abeille]

On Dec 21, 2011, at 11:40 PM, Chris Mets wrote:

> Is this truly a limitation of SQLite or am I doing something wrong?

The later. Simply create your view. Then restrict it.

In other words:

create view foo as select bar from baz

select * from foo where bar = ?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Parameters are not allowed in views

[Chris Mets]

When I execute the following SQL statement:  select * from test2 where f2 = 
@param;
 
it prompts me correctly for a parameter value.
 
However, when I try to create a view:  create view testview as select * from 
test2 where f2 = @param;
 
I receive the following error message: Parameters are not allowed in views.
 
Other SQL databases allow me to do this. When reviewing the SQLite limitations, 
this is not called out as one of the limitations. I have tried from three 
different environment (Visual Studio, SQLite Database Browser, and SQLite3 
command line utility). All give me the same error.
 
Is this truly a limitation of SQLite or am I doing something wrong?

Thanks in advance for any help,

Chris
 
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "parameters are not allowed in views"

[Arjen Markus]

Hello Alexey,

uh, yes, that is true. But preprocessing the value of
user_id, etc. should insulate you from that sort of things, right?
Unfortunately, the Tclers' Wiki does not give a ready solution for
that. But with

[string map {\; "" \[ "" \] "" $user_id]

you can get rid of most threats, right?

Regards,

Arjen

On 2009-02-27 16:01, Alexey Pechnikov wrote:
> Hello!
> 
> On Friday 27 February 2009 17:32:36 Arjen Markus wrote:
>> This is the Tcl binding, right?
>> You could replace the variable by its value using [string map]:
>>
>> db eval [string map [list USER_ID $user_id ...] $sql_statement]
>>
>> or more directly:
>>
>> db eval \
>>  "CREATE TABLE view_report_01 AS ...
>>  WHERE u.id = $user_id
>>  ..."
>>
> 
> With SQL injection security problems as result. It's not a good decision.
> 
> 
> Best regards.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Delft Hydraulics, GeoDelft, the Subsurface and Groundwater unit of TNO and 
parts of Rijkswaterstaat have joined forces in a new independent institute for 
delta technology, Deltares. Deltares combines knowledge and experience in the 
field of water, soil and the subsurface. We provide innovative solutions to 
make living in deltas, coastal areas and river basins safe, clean and 
sustainable. 

 

DISCLAIMER: This message is intended exclusively for the addressee(s) and may 
contain confidential and privileged information. If you are not the intended 
recipient please notify the sender immediately and destroy this message. 
Unauthorized use, disclosure or copying of this message is strictly prohibited.
The foundation 'Stichting Deltares', which has its seat at Delft, The 
Netherlands, Commercial Registration Number 41146461, is not liable in any way 
whatsoever for consequences and/or damages resulting from the improper, 
incomplete and untimely dispatch, receipt and/or content of this e-mail.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "parameters are not allowed in views"

[Arjen Markus]

This is the Tcl binding, right?
You could replace the variable by its value using [string map]:

db eval [string map [list USER_ID $user_id ...] $sql_statement]

or more directly:

db eval \
 "CREATE TABLE view_report_01 AS ...
 WHERE u.id = $user_id
 ..."

Regards,

Arjen
On 2009-02-27 15:28, Alexey Pechnikov wrote:
> Hello!
> 
> Is there way to careate view such as
> 
> db eval {
> CREATE TABLE view_report_01 AS
> ?SELECT s.name ?AS service_name,
> ? t_l_r.cost AS cost
> ?FROM work.users ? AS u,
> ? work.user_contracts ?AS u_c,
> ? work.user_services ?AS u_s,
> ? work.services ? AS s,
> ? telephony.telephony_log_rating AS t_l_r,
> ? telephony.telephony_log ?AS t_l
> ?WHERE u.id = $user_id
> ?AND u.id = u_c.user_id
> ?AND u_c.id = u_s.owner_id
> ?AND u_s.service_id = s.id
> ?AND u_s.id = t_l_r.user_service_id
> ?AND t_l_r.log_id = t_l.rowid
> ?AND ($date_from = '' OR julianday(date2iso($date_from)) < t_l.date_start)
> ?AND ($date_to = '' OR (julianday(date2iso($date_to)) + 1) > t_l.date_start)
> }
> 
> I have params $user_id, $date_from, $date_to. There are a lot of queries to 
> this view and is needed to place params in the view.
> 
> Best regards.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Delft Hydraulics, GeoDelft, the Subsurface and Groundwater unit of TNO and 
parts of Rijkswaterstaat have joined forces in a new independent institute for 
delta technology, Deltares. Deltares combines knowledge and experience in the 
field of water, soil and the subsurface. We provide innovative solutions to 
make living in deltas, coastal areas and river basins safe, clean and 
sustainable. 

 

DISCLAIMER: This message is intended exclusively for the addressee(s) and may 
contain confidential and privileged information. If you are not the intended 
recipient please notify the sender immediately and destroy this message. 
Unauthorized use, disclosure or copying of this message is strictly prohibited.
The foundation 'Stichting Deltares', which has its seat at Delft, The 
Netherlands, Commercial Registration Number 41146461, is not liable in any way 
whatsoever for consequences and/or damages resulting from the improper, 
incomplete and untimely dispatch, receipt and/or content of this e-mail.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "parameters are not allowed in views"

[Alexey Pechnikov]

Hello!

On Friday 27 February 2009 18:08:19 you wrote:
> [string map {\; "" \[ "" \] "" $user_id]
>
> you can get rid of most threats, right?

We can do
set param {test' sql with some injection}
puts $param
set param [db onecolumn {select quote($param)}]
puts $param

and get result
test' sql with some injection
'test'' sql with some injection'

Well, it's good. And now query 
db eval "create view view_events as select * from events where value!=$param"
is valid.

But how about construction such as
db eval {create view view_events as select * from events where value!=#param}
were #param wiil be automatically replaced by result of {select 
quote($param)}?

Best regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "parameters are not allowed in views"

[Alexey Pechnikov]

Hello!

Is there way to careate view such as

db eval {
CREATE TABLE view_report_01 AS
?SELECT s.name ?AS service_name,
? t_l_r.cost AS cost
?FROM work.users ? AS u,
? work.user_contracts ?AS u_c,
? work.user_services ?AS u_s,
? work.services ? AS s,
? telephony.telephony_log_rating AS t_l_r,
? telephony.telephony_log ?AS t_l
?WHERE u.id = $user_id
?AND u.id = u_c.user_id
?AND u_c.id = u_s.owner_id
?AND u_s.service_id = s.id
?AND u_s.id = t_l_r.user_service_id
?AND t_l_r.log_id = t_l.rowid
?AND ($date_from = '' OR julianday(date2iso($date_from)) < t_l.date_start)
?AND ($date_to = '' OR (julianday(date2iso($date_to)) + 1) > t_l.date_start)
}

I have params $user_id, $date_from, $date_to. There are a lot of queries to 
this view and is needed to place params in the view.

Best regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users