Binding an order by

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

Binding an order by

Stephen Chrzanowski
Given the query:

select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents where
Resolved=:Resolved order by :OrderBy

I wanted to bind :OrderBy with field names and conditions based on user
preferences, but I think the bind converted my order rules into a string
and ordered my results based on that string somehow.

Am I looking at a wrapper limitation or is this something SQL won't do and
I'll have to rely on concatenation?  (Which I'm not bothered by since the
sort options are hard coded)
_______________________________________________
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: Binding an order by

Igor Tandetnik-2
On 10/5/2017 2:45 PM, Stephen Chrzanowski wrote:
> Given the query:
>
> select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents where
> Resolved=:Resolved order by :OrderBy
>
> I wanted to bind :OrderBy with field names

You can't.  A bound parameter can only appear where a literal is allowed by syntax. Field names you'll have to embed directly into the query.
--
Igor Tandetnik

_______________________________________________
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: Binding an order by

Gwendal Roué-2
In reply to this post by Stephen Chrzanowski

> Le 5 oct. 2017 à 20:45, Stephen Chrzanowski <[hidden email]> a écrit :
>
> Given the query:
>
> select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents where
> Resolved=:Resolved order by :OrderBy
>
> I wanted to bind :OrderBy with field names and conditions based on user
> preferences, but I think the bind converted my order rules into a string
> and ordered my results based on that string somehow.
>
> Am I looking at a wrapper limitation or is this something SQL won't do and
> I'll have to rely on concatenation?  (Which I'm not bothered by since the
> sort options are hard coded)

Hello Stephen,

SQLite query arguments are for values only: they can't be used as placeholders for expressions such as a column name. Reference: https://sqlite.org/c3ref/bind_blob.html: <https://sqlite.org/c3ref/bind_blob.html:>

You're thus not facing a wrapper limitation: you do have to concatenate strings in order to build your dynamic SQL query.

Gwendal

_______________________________________________
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: Binding an order by

Simon Slavin-3
In reply to this post by Stephen Chrzanowski


On 5 Oct 2017, at 7:45pm, Stephen Chrzanowski <[hidden email]> wrote:

> I wanted to bind :OrderBy with field names and conditions based on user
> preferences

Binding is to values, not to column names.  If you have one ORDER BY parameter, you can only bind it to a value.  If you want a WHERE parameter you cannot bind a column name, you have to bind a value.

It looks like you need to create a string for the entire command rather than just binding values.  This, of course, introduces security problems if you allow your users to set substrings themselves rather than picking them from lists you prepare.

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: Binding an order by

Stephen Chrzanowski
In reply to this post by Gwendal Roué-2
That's all I needed to know.  Thanks.

On Thu, Oct 5, 2017 at 2:52 PM, Gwendal Roué <[hidden email]> wrote:

>
> > Le 5 oct. 2017 à 20:45, Stephen Chrzanowski <[hidden email]> a
> écrit :
> >
> > Given the query:
> >
> > select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents
> where
> > Resolved=:Resolved order by :OrderBy
> >
> > I wanted to bind :OrderBy with field names and conditions based on user
> > preferences, but I think the bind converted my order rules into a string
> > and ordered my results based on that string somehow.
> >
> > Am I looking at a wrapper limitation or is this something SQL won't do
> and
> > I'll have to rely on concatenation?  (Which I'm not bothered by since the
> > sort options are hard coded)
>
> Hello Stephen,
>
> SQLite query arguments are for values only: they can't be used as
> placeholders for expressions such as a column name. Reference:
> https://sqlite.org/c3ref/bind_blob.html: <https://sqlite.org/c3ref/
> bind_blob.html:>
>
> You're thus not facing a wrapper limitation: you do have to concatenate
> strings in order to build your dynamic SQL query.
>
> Gwendal
>
> _______________________________________________
> 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: Binding an order by

Stephen Chrzanowski
In reply to this post by Simon Slavin-3
No.  The user (Me) can only select the order through popups, not by
entering a string.  I understand the concern, but, depending on what the
user selects, the code will generate the string based on the users click.

On Thu, Oct 5, 2017 at 2:52 PM, Simon Slavin <[hidden email]> wrote:

>
>
> It looks like you need to create a string for the entire command rather
> than just binding values.  This, of course, introduces security problems if
> you allow your users to set substrings themselves rather than picking them
> from lists you prepare.
>
> Simon.
> _______________________________________________
> 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: Binding an order by

Jim Morris
In reply to this post by Igor Tandetnik-2
What you may be able to do is to use a case statement(s) which uses a
bound variable to either a column or dummy

E.g

order by case orderControlValue = 1 then column1 else "" end, ...


On 10/5/2017 11:51 AM, Igor Tandetnik wrote:

> On 10/5/2017 2:45 PM, Stephen Chrzanowski wrote:
>> Given the query:
>>
>> select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents
>> where
>> Resolved=:Resolved order by :OrderBy
>>
>> I wanted to bind :OrderBy with field names
>
> You can't.  A bound parameter can only appear where a literal is
> allowed by syntax. Field names you'll have to embed directly into the
> query.

_______________________________________________
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: Binding an order by

Simon Slavin-3
In reply to this post by Stephen Chrzanowski


On 5 Oct 2017, at 7:59pm, Stephen Chrzanowski <[hidden email]> wrote:

> No.  The user (Me) can only select the order through popups, not by
> entering a string.  I understand the concern, but, depending on what the
> user selects, the code will generate the string based on the users click.

Very good.

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: Binding an order by

Stephen Chrzanowski
In reply to this post by Jim Morris
Interesting idea, and one for the books, but, in this case, the sort order
is complicated by nothing being fixed.  The primary sort concern is whether
I'm grouping priorities of the tickets together or not.  If grouped, the
priority order is considered first [ order by Priority=0,Priority, ].  If I
have an item that is zero priority, it ends up at the bottom of the list.
If I'm not grouping, then priority is not part of the sort.  After that,
and always included, is the order by which I want to see my list by
(EventID, Airline, ContactInfo, etc).

..  but I have a thought about that... I'll need to check it out tonight
after work or tomorrow....


On Thu, Oct 5, 2017 at 3:13 PM, Jim Morris <[hidden email]> wrote:

> What you may be able to do is to use a case statement(s) which uses a
> bound variable to either a column or dummy
>
> E.g
>
> order by case orderControlValue = 1 then column1 else "" end, ...
>
>
> On 10/5/2017 11:51 AM, Igor Tandetnik wrote:
>
>> On 10/5/2017 2:45 PM, Stephen Chrzanowski wrote:
>>
>>> Given the query:
>>>
>>> select EventID, Airline, ContactInfo,TicketID,CreateDate from tEvents
>>> where
>>> Resolved=:Resolved order by :OrderBy
>>>
>>> I wanted to bind :OrderBy with field names
>>>
>>
>> You can't.  A bound parameter can only appear where a literal is allowed
>> by syntax. Field names you'll have to embed directly into the query.
>>
>
> _______________________________________________
> 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