parameterized query with wildcard

classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

parameterized query with wildcard

Mike Kerner
Has anybody built any queries that use both parameters and wildcards, e.g.
in a LIKE statement?

SELECT * FROM foo WHERE bar LIKE %:1%

I've tried the above (error), I've tried '%':1'%' (error), and I've tried
appending the % to the container I'm passing as my :1 (doesn't work).

I really don't want to do this the unsafe way.
--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Dave Kilroy
Mike, assuming you are searching the db with parameter pSearchTerm, try something like this:


put "%" & pSearchTerm & "%" into tSearchTerm
put "SELECT * FROM foo WHERE bar LIKE :1" into tQuery
get revDataFromQuery(tab, return, sDBID, tQuery, "tSearchTerm")


"The first 90% of the task takes 90% of the time, and the last 10% takes the other 90% of the time."
Peter M. Brigham
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Andrew Kluthe-2
Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking
extra SQL into your search box like an injection style attack, or does it
just plop whatever you give in there no questions asked? Just curious. I
have always been spoiled by SQLYoga or rolled my DB interfaces up into API
servers of some kind.

On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy <[hidden email]>
wrote:

> Mike, assuming you are searching the db with parameter pSearchTerm, try
> something like this:
>
>
> put "%" & pSearchTerm & "%" into tSearchTerm
> put "SELECT * FROM foo WHERE bar LIKE :1" into tQuery
> get revDataFromQuery(tab, return, sDBID, tQuery, "tSearchTerm")
>
>
>
>
>
>
> -----
> "The difference between genius and stupidity is; genius has its limits." -
> Albert Einstein
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Andrew Kluthe-2
Should have read, *proper escaping*.

On Tue, Jul 28, 2015 at 11:17 AM Andrew Kluthe <[hidden email]> wrote:

> Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking
> extra SQL into your search box like an injection style attack, or does it
> just plop whatever you give in there no questions asked? Just curious. I
> have always been spoiled by SQLYoga or rolled my DB interfaces up into API
> servers of some kind.
>
> On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy <[hidden email]>
> wrote:
>
>> Mike, assuming you are searching the db with parameter pSearchTerm, try
>> something like this:
>>
>>
>> put "%" & pSearchTerm & "%" into tSearchTerm
>> put "SELECT * FROM foo WHERE bar LIKE :1" into tQuery
>> get revDataFromQuery(tab, return, sDBID, tQuery, "tSearchTerm")
>>
>>
>>
>>
>>
>>
>> -----
>> "The difference between genius and stupidity is; genius has its limits."
>> - Albert Einstein
>> --
>> View this message in context:
>> http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html
>> Sent from the Revolution - User mailing list archive at Nabble.com.
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Mike Kerner
Dave, sorry, I thought I mentioned trying that.  It does not work.

Andrew, yes, if you use a parameterized query, you do not have to
escape/sanitize your parameters.  If you append them to build a query, you
do.

On Tue, Jul 28, 2015 at 12:18 PM, Andrew Kluthe <[hidden email]> wrote:

> Should have read, *proper escaping*.
>
> On Tue, Jul 28, 2015 at 11:17 AM Andrew Kluthe <[hidden email]> wrote:
>
> > Does revDataFromQuery do any sanitizing/proper to prevent me from
> sneaking
> > extra SQL into your search box like an injection style attack, or does it
> > just plop whatever you give in there no questions asked? Just curious. I
> > have always been spoiled by SQLYoga or rolled my DB interfaces up into
> API
> > servers of some kind.
> >
> > On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy <
> [hidden email]>
> > wrote:
> >
> >> Mike, assuming you are searching the db with parameter pSearchTerm, try
> >> something like this:
> >>
> >>
> >> put "%" & pSearchTerm & "%" into tSearchTerm
> >> put "SELECT * FROM foo WHERE bar LIKE :1" into tQuery
> >> get revDataFromQuery(tab, return, sDBID, tQuery, "tSearchTerm")
> >>
> >>
> >>
> >>
> >>
> >>
> >> -----
> >> "The difference between genius and stupidity is; genius has its limits."
> >> - Albert Einstein
> >> --
> >> View this message in context:
> >>
> http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html
> >> Sent from the Revolution - User mailing list archive at Nabble.com.
> >>
> >> _______________________________________________
> >> use-livecode mailing list
> >> [hidden email]
> >> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>
> >
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Mike Kerner
Dave,
I take that back - I must have had a typo the first time I tried it.
Appending the wildcards to the search parameter does work.

On Tue, Jul 28, 2015 at 12:28 PM, Mike Kerner <[hidden email]>
wrote:

> Dave, sorry, I thought I mentioned trying that.  It does not work.
>
> Andrew, yes, if you use a parameterized query, you do not have to
> escape/sanitize your parameters.  If you append them to build a query, you
> do.
>
> On Tue, Jul 28, 2015 at 12:18 PM, Andrew Kluthe <[hidden email]> wrote:
>
>> Should have read, *proper escaping*.
>>
>> On Tue, Jul 28, 2015 at 11:17 AM Andrew Kluthe <[hidden email]> wrote:
>>
>> > Does revDataFromQuery do any sanitizing/proper to prevent me from
>> sneaking
>> > extra SQL into your search box like an injection style attack, or does
>> it
>> > just plop whatever you give in there no questions asked? Just curious. I
>> > have always been spoiled by SQLYoga or rolled my DB interfaces up into
>> API
>> > servers of some kind.
>> >
>> > On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy <
>> [hidden email]>
>> > wrote:
>> >
>> >> Mike, assuming you are searching the db with parameter pSearchTerm, try
>> >> something like this:
>> >>
>> >>
>> >> put "%" & pSearchTerm & "%" into tSearchTerm
>> >> put "SELECT * FROM foo WHERE bar LIKE :1" into tQuery
>> >> get revDataFromQuery(tab, return, sDBID, tQuery, "tSearchTerm")
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> -----
>> >> "The difference between genius and stupidity is; genius has its
>> limits."
>> >> - Albert Einstein
>> >> --
>> >> View this message in context:
>> >>
>> http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html
>> >> Sent from the Revolution - User mailing list archive at Nabble.com.
>> >>
>> >> _______________________________________________
>> >> use-livecode mailing list
>> >> [hidden email]
>> >> Please visit this url to subscribe, unsubscribe and manage your
>> >> subscription preferences:
>> >> http://lists.runrev.com/mailman/listinfo/use-livecode
>> >>
>> >
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>    and did a little diving.
> And God said, "This is good."
>



--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Bob Sneidar-2
In reply to this post by Mike Kerner
Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.

If you are searching for a value at the beginning, LIKE ‘:1%’ or at the end, LIKE ‘%:1’

If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column LIKE ‘%:1’

HTH

Bob S


> On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]> wrote:
>
> Has anybody built any queries that use both parameters and wildcards, e.g.
> in a LIKE statement?
>
> SELECT * FROM foo WHERE bar LIKE %:1%
>
> I've tried the above (error), I've tried '%':1'%' (error), and I've tried
> appending the % to the container I'm passing as my :1 (doesn't work).
>
> I really don't want to do this the unsafe way.
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>   and did a little diving.
> And God said, "This is good."
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Mike Kerner
Nope.  That doesn't work, Bob.  That returns nothing.

On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <[hidden email]>
wrote:

> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
>
> If you are searching for a value at the beginning, LIKE ‘:1%’ or at the
> end, LIKE ‘%:1’
>
> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column
> LIKE ‘%:1’
>
> HTH
>
> Bob S
>
>
> > On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]>
> wrote:
> >
> > Has anybody built any queries that use both parameters and wildcards,
> e.g.
> > in a LIKE statement?
> >
> > SELECT * FROM foo WHERE bar LIKE %:1%
> >
> > I've tried the above (error), I've tried '%':1'%' (error), and I've tried
> > appending the % to the container I'm passing as my :1 (doesn't work).
> >
> > I really don't want to do this the unsafe way.
> > --
> > On the first day, God created the heavens and the Earth
> > On the second day, God created the oceans.
> > On the third day, God put the animals on hold for a few hours,
> >   and did a little diving.
> > And God said, "This is good."
> > _______________________________________________
> > use-livecode mailing list
> > [hidden email]
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Mike Kerner
If I was guessing, my hunch would be that including the single-quotes is
going to make the db look for strings containing %:1%, instead of using the
wildcards and the parameter.

On Wed, Jul 29, 2015 at 9:31 AM, Mike Kerner <[hidden email]>
wrote:

> Nope.  That doesn't work, Bob.  That returns nothing.
>
> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <[hidden email]>
> wrote:
>
>> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
>>
>> If you are searching for a value at the beginning, LIKE ‘:1%’ or at the
>> end, LIKE ‘%:1’
>>
>> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column
>> LIKE ‘%:1’
>>
>> HTH
>>
>> Bob S
>>
>>
>> > On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]>
>> wrote:
>> >
>> > Has anybody built any queries that use both parameters and wildcards,
>> e.g.
>> > in a LIKE statement?
>> >
>> > SELECT * FROM foo WHERE bar LIKE %:1%
>> >
>> > I've tried the above (error), I've tried '%':1'%' (error), and I've
>> tried
>> > appending the % to the container I'm passing as my :1 (doesn't work).
>> >
>> > I really don't want to do this the unsafe way.
>> > --
>> > On the first day, God created the heavens and the Earth
>> > On the second day, God created the oceans.
>> > On the third day, God put the animals on hold for a few hours,
>> >   and did a little diving.
>> > And God said, "This is good."
>> > _______________________________________________
>> > use-livecode mailing list
>> > [hidden email]
>> > Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> > http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>    and did a little diving.
> And God said, "This is good."
>



--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

PystCat
Why not just use merge...?

Put "John" into tVal
Put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]])") into pSQL
OR
put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]]%)") into pSQL

I do this for all of my queries and it works fine.

Paul




> On Jul 29, 2015, at 9:45 AM, Mike Kerner <[hidden email]> wrote:
>
> If I was guessing, my hunch would be that including the single-quotes is
> going to make the db look for strings containing %:1%, instead of using the
> wildcards and the parameter.
>
> On Wed, Jul 29, 2015 at 9:31 AM, Mike Kerner <[hidden email]>
> wrote:
>
>> Nope.  That doesn't work, Bob.  That returns nothing.
>>
>> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <[hidden email]>
>> wrote:
>>
>>> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
>>>
>>> If you are searching for a value at the beginning, LIKE ‘:1%’ or at the
>>> end, LIKE ‘%:1’
>>>
>>> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column
>>> LIKE ‘%:1’
>>>
>>> HTH
>>>
>>> Bob S
>>>
>>>
>>>> On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]>
>>> wrote:
>>>>
>>>> Has anybody built any queries that use both parameters and wildcards,
>>> e.g.
>>>> in a LIKE statement?
>>>>
>>>> SELECT * FROM foo WHERE bar LIKE %:1%
>>>>
>>>> I've tried the above (error), I've tried '%':1'%' (error), and I've
>>> tried
>>>> appending the % to the container I'm passing as my :1 (doesn't work).
>>>>
>>>> I really don't want to do this the unsafe way.
>>>> --
>>>> On the first day, God created the heavens and the Earth
>>>> On the second day, God created the oceans.
>>>> On the third day, God put the animals on hold for a few hours,
>>>>  and did a little diving.
>>>> And God said, "This is good."
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> [hidden email]
>>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>
>>
>>
>>
>> --
>> On the first day, God created the heavens and the Earth
>> On the second day, God created the oceans.
>> On the third day, God put the animals on hold for a few hours,
>>   and did a little diving.
>> And God said, "This is good."
>>
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>   and did a little diving.
> And God said, "This is good."
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Mike Kerner
The reason for using parameterized queries instead of either merging or
appending is because of SQL injection.

On Wed, Jul 29, 2015 at 10:18 AM, PystCat <[hidden email]> wrote:

> Why not just use merge...?
>
> Put "John" into tVal
> Put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]])") into pSQL
> OR
> put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]]%)") into pSQL
>
> I do this for all of my queries and it works fine.
>
> Paul
>
>
>
>
> > On Jul 29, 2015, at 9:45 AM, Mike Kerner <[hidden email]>
> wrote:
> >
> > If I was guessing, my hunch would be that including the single-quotes is
> > going to make the db look for strings containing %:1%, instead of using
> the
> > wildcards and the parameter.
> >
> > On Wed, Jul 29, 2015 at 9:31 AM, Mike Kerner <[hidden email]>
> > wrote:
> >
> >> Nope.  That doesn't work, Bob.  That returns nothing.
> >>
> >> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <
> [hidden email]>
> >> wrote:
> >>
> >>> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
> >>>
> >>> If you are searching for a value at the beginning, LIKE ‘:1%’ or at the
> >>> end, LIKE ‘%:1’
> >>>
> >>> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column
> >>> LIKE ‘%:1’
> >>>
> >>> HTH
> >>>
> >>> Bob S
> >>>
> >>>
> >>>> On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]>
> >>> wrote:
> >>>>
> >>>> Has anybody built any queries that use both parameters and wildcards,
> >>> e.g.
> >>>> in a LIKE statement?
> >>>>
> >>>> SELECT * FROM foo WHERE bar LIKE %:1%
> >>>>
> >>>> I've tried the above (error), I've tried '%':1'%' (error), and I've
> >>> tried
> >>>> appending the % to the container I'm passing as my :1 (doesn't work).
> >>>>
> >>>> I really don't want to do this the unsafe way.
> >>>> --
> >>>> On the first day, God created the heavens and the Earth
> >>>> On the second day, God created the oceans.
> >>>> On the third day, God put the animals on hold for a few hours,
> >>>>  and did a little diving.
> >>>> And God said, "This is good."
> >>>> _______________________________________________
> >>>> use-livecode mailing list
> >>>> [hidden email]
> >>>> Please visit this url to subscribe, unsubscribe and manage your
> >>> subscription preferences:
> >>>> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>>
> >>> _______________________________________________
> >>> use-livecode mailing list
> >>> [hidden email]
> >>> Please visit this url to subscribe, unsubscribe and manage your
> >>> subscription preferences:
> >>> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>>
> >>
> >>
> >>
> >> --
> >> On the first day, God created the heavens and the Earth
> >> On the second day, God created the oceans.
> >> On the third day, God put the animals on hold for a few hours,
> >>   and did a little diving.
> >> And God said, "This is good."
> >>
> >
> >
> >
> > --
> > On the first day, God created the heavens and the Earth
> > On the second day, God created the oceans.
> > On the third day, God put the animals on hold for a few hours,
> >   and did a little diving.
> > And God said, "This is good."
> > _______________________________________________
> > use-livecode mailing list
> > [hidden email]
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



--
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Bob Sneidar-2
In reply to this post by Mike Kerner
If you copied and pasted it may be that the small quotes are not the right characters. I have used this query successfully myself so I may have mistyped something.

Bob S


> On Jul 29, 2015, at 06:31 , Mike Kerner <[hidden email]> wrote:
>
> Nope.  That doesn't work, Bob.  That returns nothing.
>
> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <[hidden email]>
> wrote:
>
>> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
>>
>> If you are searching for a value at the beginning, LIKE ‘:1%’ or at the
>> end, LIKE ‘%:1’
>>
>> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column
>> LIKE ‘%:1’
>>
>> HTH
>>
>> Bob S
>>
>>
>>> On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]>
>> wrote:
>>>
>>> Has anybody built any queries that use both parameters and wildcards,
>> e.g.
>>> in a LIKE statement?
>>>
>>> SELECT * FROM foo WHERE bar LIKE %:1%
>>>
>>> I've tried the above (error), I've tried '%':1'%' (error), and I've tried
>>> appending the % to the container I'm passing as my :1 (doesn't work).
>>>
>>> I really don't want to do this the unsafe way.
>>> --
>>> On the first day, God created the heavens and the Earth
>>> On the second day, God created the oceans.
>>> On the third day, God put the animals on hold for a few hours,
>>>  and did a little diving.
>>> And God said, "This is good."
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>   and did a little diving.
> And God said, "This is good."
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

PystCat
In reply to this post by Mike Kerner
Not a problem... Scrub the variable before the merge... It's what I do as well.  I have a function that takes the input and scrubs it... I'm away for another week but if you're interested, when I get back I can post the handler.



> On Jul 29, 2015, at 10:35 AM, Mike Kerner <[hidden email]> wrote:
>
> The reason for using parameterized queries instead of either merging or
> appending is because of SQL injection.
>
>> On Wed, Jul 29, 2015 at 10:18 AM, PystCat <[hidden email]> wrote:
>>
>> Why not just use merge...?
>>
>> Put "John" into tVal
>> Put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]])") into pSQL
>> OR
>> put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]]%)") into pSQL
>>
>> I do this for all of my queries and it works fine.
>>
>> Paul
>>
>>
>>
>>
>>>> On Jul 29, 2015, at 9:45 AM, Mike Kerner <[hidden email]>
>>> wrote:
>>>
>>> If I was guessing, my hunch would be that including the single-quotes is
>>> going to make the db look for strings containing %:1%, instead of using
>> the
>>> wildcards and the parameter.
>>>
>>> On Wed, Jul 29, 2015 at 9:31 AM, Mike Kerner <[hidden email]>
>>> wrote:
>>>
>>>> Nope.  That doesn't work, Bob.  That returns nothing.
>>>>
>>>> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <
>> [hidden email]>
>>>> wrote:
>>>>
>>>>> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
>>>>>
>>>>> If you are searching for a value at the beginning, LIKE ‘:1%’ or at the
>>>>> end, LIKE ‘%:1’
>>>>>
>>>>> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column
>>>>> LIKE ‘%:1’
>>>>>
>>>>> HTH
>>>>>
>>>>> Bob S
>>>>>
>>>>>
>>>>>>> On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]>
>>>>>> wrote:
>>>>>>
>>>>>> Has anybody built any queries that use both parameters and wildcards,
>>>>> e.g.
>>>>>> in a LIKE statement?
>>>>>>
>>>>>> SELECT * FROM foo WHERE bar LIKE %:1%
>>>>>>
>>>>>> I've tried the above (error), I've tried '%':1'%' (error), and I've
>>>>> tried
>>>>>> appending the % to the container I'm passing as my :1 (doesn't work).
>>>>>>
>>>>>> I really don't want to do this the unsafe way.
>>>>>> --
>>>>>> On the first day, God created the heavens and the Earth
>>>>>> On the second day, God created the oceans.
>>>>>> On the third day, God put the animals on hold for a few hours,
>>>>>> and did a little diving.
>>>>>> And God said, "This is good."
>>>>>> _______________________________________________
>>>>>> use-livecode mailing list
>>>>>> [hidden email]
>>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>>> subscription preferences:
>>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>>>
>>>>> _______________________________________________
>>>>> use-livecode mailing list
>>>>> [hidden email]
>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>>> subscription preferences:
>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>>
>>>>
>>>>
>>>> --
>>>> On the first day, God created the heavens and the Earth
>>>> On the second day, God created the oceans.
>>>> On the third day, God put the animals on hold for a few hours,
>>>>  and did a little diving.
>>>> And God said, "This is good."
>>>
>>>
>>>
>>> --
>>> On the first day, God created the heavens and the Earth
>>> On the second day, God created the oceans.
>>> On the third day, God put the animals on hold for a few hours,
>>>  and did a little diving.
>>> And God said, "This is good."
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>   and did a little diving.
> And God said, "This is good."
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Peter Haworth
In reply to this post by Mike Kerner
If you are specifying a literal value with LIKE, then you need the single
quotes or you will get an error.  If you are using a parameter variable
containing the literal, then no single quotes needed.  Including the :1 in
quotes makes the query look for a string containing :1, not the contents of
the variable referred to by :1.

On Wed, Jul 29, 2015 at 6:33 AM Mike Kerner <[hidden email]>
wrote:

> Nope.  That doesn't work, Bob.  That returns nothing.
>
> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <[hidden email]>
> wrote:
>
> > Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
> >
> > If you are searching for a value at the beginning, LIKE ‘:1%’ or at the
> > end, LIKE ‘%:1’
> >
> > If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column
> > LIKE ‘%:1’
> >
> > HTH
> >
> > Bob S
> >
> >
> > > On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]>
> > wrote:
> > >
> > > Has anybody built any queries that use both parameters and wildcards,
> > e.g.
> > > in a LIKE statement?
> > >
> > > SELECT * FROM foo WHERE bar LIKE %:1%
> > >
> > > I've tried the above (error), I've tried '%':1'%' (error), and I've
> tried
> > > appending the % to the container I'm passing as my :1 (doesn't work).
> > >
> > > I really don't want to do this the unsafe way.
> > > --
> > > On the first day, God created the heavens and the Earth
> > > On the second day, God created the oceans.
> > > On the third day, God put the animals on hold for a few hours,
> > >   and did a little diving.
> > > And God said, "This is good."
> > > _______________________________________________
> > > use-livecode mailing list
> > > [hidden email]
> > > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > > http://lists.runrev.com/mailman/listinfo/use-livecode
> >
> > _______________________________________________
> > use-livecode mailing list
> > [hidden email]
> > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
> >
>
>
>
> --
> On the first day, God created the heavens and the Earth
> On the second day, God created the oceans.
> On the third day, God put the animals on hold for a few hours,
>    and did a little diving.
> And God said, "This is good."
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Peter Haworth
In reply to this post by PystCat
But why bother?  You're already putting the value into a variable so all
that's required is use :1 and append the variable name to the revxxx call.
On Wed, Jul 29, 2015 at 8:29 AM PystCat <[hidden email]> wrote:

> Not a problem... Scrub the variable before the merge... It's what I do as
> well.  I have a function that takes the input and scrubs it... I'm away for
> another week but if you're interested, when I get back I can post the
> handler.
>
>
>
> > On Jul 29, 2015, at 10:35 AM, Mike Kerner <[hidden email]>
> wrote:
> >
> > The reason for using parameterized queries instead of either merging or
> > appending is because of SQL injection.
> >
> >> On Wed, Jul 29, 2015 at 10:18 AM, PystCat <[hidden email]> wrote:
> >>
> >> Why not just use merge...?
> >>
> >> Put "John" into tVal
> >> Put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]])") into pSQL
> >> OR
> >> put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]]%)") into pSQL
> >>
> >> I do this for all of my queries and it works fine.
> >>
> >> Paul
> >>
> >>
> >>
> >>
> >>>> On Jul 29, 2015, at 9:45 AM, Mike Kerner <[hidden email]>
> >>> wrote:
> >>>
> >>> If I was guessing, my hunch would be that including the single-quotes
> is
> >>> going to make the db look for strings containing %:1%, instead of using
> >> the
> >>> wildcards and the parameter.
> >>>
> >>> On Wed, Jul 29, 2015 at 9:31 AM, Mike Kerner <
> [hidden email]>
> >>> wrote:
> >>>
> >>>> Nope.  That doesn't work, Bob.  That returns nothing.
> >>>>
> >>>> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <
> >> [hidden email]>
> >>>> wrote:
> >>>>
> >>>>> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
> >>>>>
> >>>>> If you are searching for a value at the beginning, LIKE ‘:1%’ or at
> the
> >>>>> end, LIKE ‘%:1’
> >>>>>
> >>>>> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR
> column
> >>>>> LIKE ‘%:1’
> >>>>>
> >>>>> HTH
> >>>>>
> >>>>> Bob S
> >>>>>
> >>>>>
> >>>>>>> On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]
> >
> >>>>>> wrote:
> >>>>>>
> >>>>>> Has anybody built any queries that use both parameters and
> wildcards,
> >>>>> e.g.
> >>>>>> in a LIKE statement?
> >>>>>>
> >>>>>> SELECT * FROM foo WHERE bar LIKE %:1%
> >>>>>>
> >>>>>> I've tried the above (error), I've tried '%':1'%' (error), and I've
> >>>>> tried
> >>>>>> appending the % to the container I'm passing as my :1 (doesn't
> work).
> >>>>>>
> >>>>>> I really don't want to do this the unsafe way.
> >>>>>> --
> >>>>>> On the first day, God created the heavens and the Earth
> >>>>>> On the second day, God created the oceans.
> >>>>>> On the third day, God put the animals on hold for a few hours,
> >>>>>> and did a little diving.
> >>>>>> And God said, "This is good."
> >>>>>> _______________________________________________
> >>>>>> use-livecode mailing list
> >>>>>> [hidden email]
> >>>>>> Please visit this url to subscribe, unsubscribe and manage your
> >>>>> subscription preferences:
> >>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>>>>
> >>>>> _______________________________________________
> >>>>> use-livecode mailing list
> >>>>> [hidden email]
> >>>>> Please visit this url to subscribe, unsubscribe and manage your
> >>>>> subscription preferences:
> >>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> On the first day, God created the heavens and the Earth
> >>>> On the second day, God created the oceans.
> >>>> On the third day, God put the animals on hold for a few hours,
> >>>>  and did a little diving.
> >>>> And God said, "This is good."
> >>>
> >>>
> >>>
> >>> --
> >>> On the first day, God created the heavens and the Earth
> >>> On the second day, God created the oceans.
> >>> On the third day, God put the animals on hold for a few hours,
> >>>  and did a little diving.
> >>> And God said, "This is good."
> >>> _______________________________________________
> >>> use-livecode mailing list
> >>> [hidden email]
> >>> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >>> http://lists.runrev.com/mailman/listinfo/use-livecode
> >>
> >> _______________________________________________
> >> use-livecode mailing list
> >> [hidden email]
> >> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >> http://lists.runrev.com/mailman/listinfo/use-livecode
> >
> >
> >
> > --
> > On the first day, God created the heavens and the Earth
> > On the second day, God created the oceans.
> > On the third day, God put the animals on hold for a few hours,
> >   and did a little diving.
> > And God said, "This is good."
> > _______________________________________________
> > use-livecode mailing list
> > [hidden email]
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

PystCat
When I get that value from the user it is scrubbed and then put into the SQL with the merge.




> On Jul 29, 2015, at 12:18 PM, Peter Haworth <[hidden email]> wrote:
>
> But why bother?  You're already putting the value into a variable so all
> that's required is use :1 and append the variable name to the revxxx call.
>> On Wed, Jul 29, 2015 at 8:29 AM PystCat <[hidden email]> wrote:
>>
>> Not a problem... Scrub the variable before the merge... It's what I do as
>> well.  I have a function that takes the input and scrubs it... I'm away for
>> another week but if you're interested, when I get back I can post the
>> handler.
>>
>>
>>
>>>> On Jul 29, 2015, at 10:35 AM, Mike Kerner <[hidden email]>
>>> wrote:
>>>
>>> The reason for using parameterized queries instead of either merging or
>>> appending is because of SQL injection.
>>>
>>>> On Wed, Jul 29, 2015 at 10:18 AM, PystCat <[hidden email]> wrote:
>>>>
>>>> Why not just use merge...?
>>>>
>>>> Put "John" into tVal
>>>> Put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]])") into pSQL
>>>> OR
>>>> put merge("SELECT * FROM foo WHERE(bar LIKE %[[tVal]]%)") into pSQL
>>>>
>>>> I do this for all of my queries and it works fine.
>>>>
>>>> Paul
>>>>
>>>>
>>>>
>>>>
>>>>>> On Jul 29, 2015, at 9:45 AM, Mike Kerner <[hidden email]>
>>>>> wrote:
>>>>>
>>>>> If I was guessing, my hunch would be that including the single-quotes
>> is
>>>>> going to make the db look for strings containing %:1%, instead of using
>>>> the
>>>>> wildcards and the parameter.
>>>>>
>>>>> On Wed, Jul 29, 2015 at 9:31 AM, Mike Kerner <
>> [hidden email]>
>>>>> wrote:
>>>>>
>>>>>> Nope.  That doesn't work, Bob.  That returns nothing.
>>>>>>
>>>>>> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <
>>>> [hidden email]>
>>>>>> wrote:
>>>>>>
>>>>>>> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
>>>>>>>
>>>>>>> If you are searching for a value at the beginning, LIKE ‘:1%’ or at
>> the
>>>>>>> end, LIKE ‘%:1’
>>>>>>>
>>>>>>> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR
>> column
>>>>>>> LIKE ‘%:1’
>>>>>>>
>>>>>>> HTH
>>>>>>>
>>>>>>> Bob S
>>>>>>>
>>>>>>>
>>>>>>>>> On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]
>>>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>> Has anybody built any queries that use both parameters and
>> wildcards,
>>>>>>> e.g.
>>>>>>>> in a LIKE statement?
>>>>>>>>
>>>>>>>> SELECT * FROM foo WHERE bar LIKE %:1%
>>>>>>>>
>>>>>>>> I've tried the above (error), I've tried '%':1'%' (error), and I've
>>>>>>> tried
>>>>>>>> appending the % to the container I'm passing as my :1 (doesn't
>> work).
>>>>>>>>
>>>>>>>> I really don't want to do this the unsafe way.
>>>>>>>> --
>>>>>>>> On the first day, God created the heavens and the Earth
>>>>>>>> On the second day, God created the oceans.
>>>>>>>> On the third day, God put the animals on hold for a few hours,
>>>>>>>> and did a little diving.
>>>>>>>> And God said, "This is good."
>>>>>>>> _______________________________________________
>>>>>>>> use-livecode mailing list
>>>>>>>> [hidden email]
>>>>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>>>>> subscription preferences:
>>>>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> use-livecode mailing list
>>>>>>> [hidden email]
>>>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>>>>> subscription preferences:
>>>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> On the first day, God created the heavens and the Earth
>>>>>> On the second day, God created the oceans.
>>>>>> On the third day, God put the animals on hold for a few hours,
>>>>>> and did a little diving.
>>>>>> And God said, "This is good."
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> On the first day, God created the heavens and the Earth
>>>>> On the second day, God created the oceans.
>>>>> On the third day, God put the animals on hold for a few hours,
>>>>> and did a little diving.
>>>>> And God said, "This is good."
>>>>> _______________________________________________
>>>>> use-livecode mailing list
>>>>> [hidden email]
>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>> subscription preferences:
>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>>
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> [hidden email]
>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>> subscription preferences:
>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>
>>>
>>>
>>> --
>>> On the first day, God created the heavens and the Earth
>>> On the second day, God created the oceans.
>>> On the third day, God put the animals on hold for a few hours,
>>>  and did a little diving.
>>> And God said, "This is good."
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Bob Sneidar-2
In reply to this post by Peter Haworth
How odd. I am thinking now, that because I am passing these query arguements to sqlYoga it is doing the macro replacement instead of SQL. Now that I think of it, I have never used this  in a direct SQL query. I am not even sure how to construct it. Is this a web server convention? I cannot see how you would inject the values in a direct query.

Bob S


> On Jul 29, 2015, at 09:16 , Peter Haworth <[hidden email]> wrote:
>
> If you are specifying a literal value with LIKE, then you need the single
> quotes or you will get an error.  If you are using a parameter variable
> containing the literal, then no single quotes needed.  Including the :1 in
> quotes makes the query look for a string containing :1, not the contents of
> the variable referred to by :1.
>
> On Wed, Jul 29, 2015 at 6:33 AM Mike Kerner <[hidden email]>
> wrote:
>
>> Nope.  That doesn't work, Bob.  That returns nothing.
>>
>> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <[hidden email]>
>> wrote:
>>
>>> Should be LIKE ‘:1’ or for wild cards LIKE ‘%:1%’.
>>>
>>> If you are searching for a value at the beginning, LIKE ‘:1%’ or at the
>>> end, LIKE ‘%:1’
>>>
>>> If searching for all, column LIKE ‘%:1%’ OR column LIKE ‘:1%’ OR column
>>> LIKE ‘%:1’
>>>
>>> HTH
>>>
>>> Bob S
>>>
>>>
>>>> On Jul 28, 2015, at 08:16 , Mike Kerner <[hidden email]>
>>> wrote:
>>>>
>>>> Has anybody built any queries that use both parameters and wildcards,
>>> e.g.
>>>> in a LIKE statement?
>>>>
>>>> SELECT * FROM foo WHERE bar LIKE %:1%
>>>>
>>>> I've tried the above (error), I've tried '%':1'%' (error), and I've
>> tried
>>>> appending the % to the container I'm passing as my :1 (doesn't work).
>>>>
>>>> I really don't want to do this the unsafe way.
>>>> --
>>>> On the first day, God created the heavens and the Earth
>>>> On the second day, God created the oceans.
>>>> On the third day, God put the animals on hold for a few hours,
>>>>  and did a little diving.
>>>> And God said, "This is good."
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> [hidden email]
>>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>
>>
>>
>>
>> --
>> On the first day, God created the heavens and the Earth
>> On the second day, God created the oceans.
>> On the third day, God put the animals on hold for a few hours,
>>   and did a little diving.
>> And God said, "This is good."
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Bob Sneidar-2
Okay I see my confusion. I can use a statement like:

select * from customers where customername LIKE '%int%’

This works. It seems however that when using parameter substitution it does not. In sqlYoga I can use:

put sqlquery_createObject(“customers”) into qCustomerObject
put “customer name LIKE ‘%:1%’” into tConditions
put “int” into aBindings [1]
sqlquery_set qCustomerObject, “conditions”, tConditions
sqlquery_set qCustomerObject, “condition bindings”, aBindings
sqlquery_retrieveAsArray qCustomerObject, aCustomerData

This also works. I should also mention that my mail program *DOES* substitute plain quotes for smart ones. I am disabling that “feature” now.

Bob S



> On Jul 29, 2015, at 14:35 , Bob Sneidar <[hidden email]> wrote:
>
> How odd. I am thinking now, that because I am passing these query arguements to sqlYoga it is doing the macro replacement instead of SQL. Now that I think of it, I have never used this  in a direct SQL query. I am not even sure how to construct it. Is this a web server convention? I cannot see how you would inject the values in a direct query.
>
> Bob S

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Bob Sneidar-2
Turns out it is done in the system settings under keyboard in case anyone else needs to do the same.

Bob S


On Jul 29, 2015, at 15:06 , Bob Sneidar <[hidden email]<mailto:[hidden email]>> wrote:

I should also mention that my mail program *DOES* substitute plain quotes for smart ones. I am disabling that “feature” now.

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: parameterized query with wildcard

Bob Sneidar-2
In reply to this post by Andrew Kluthe-2
Yes it does. If you use the placeholder method (I am not really sure what to call it at this point) then sqlYoga sanitizes the strings for you. I've inserted records with any number of characters using this method without any problems reading in or out of the database.

I'm not sure if a direct query like "address = '\\bobscomputer\scans'" is sanitized. For the sake of standardization I always use the placeholder method.

Bob S


> On Jul 28, 2015, at 09:17 , Andrew Kluthe <[hidden email]> wrote:
>
> Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking
> extra SQL into your search box like an injection style attack, or does it
> just plop whatever you give in there no questions asked? Just curious. I
> have always been spoiled by SQLYoga or rolled my DB interfaces up into API
> servers of some kind.
>
> On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy <[hidden email]>
> wrote:
>
>> Mike, assuming you are searching the db with parameter pSearchTerm, try
>> something like this:
>>
>>
>> put "%" & pSearchTerm & "%" into tSearchTerm
>> put "SELECT * FROM foo WHERE bar LIKE :1" into tQuery
>> get revDataFromQuery(tab, return, sDBID, tQuery, "tSearchTerm")
>>
>>
>>
>>
>>
>>
>> -----
>> "The difference between genius and stupidity is; genius has its limits." -
>> Albert Einstein
>> --
>> View this message in context:
>> http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html
>> Sent from the Revolution - User mailing list archive at Nabble.com.
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
12