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 <pete at lcsql.com> 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 <pystcat at gmail.com> 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 <MikeKerner at roadrunner.com>
>>> 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 <pystcat at gmail.com> 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 <MikeKerner at roadrunner.com>
>>>>> 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 <
>> MikeKerner at roadrunner.com>
>>>>> wrote:
>>>>> 
>>>>>> Nope.  That doesn't work, Bob.  That returns nothing.
>>>>>> 
>>>>>> On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar <
>>>> bobsneidar at iotecdigital.com>
>>>>>> 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 <MikeKerner at roadrunner.com
>>> 
>>>>>>>> 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
>>>>>>>> use-livecode at lists.runrev.com
>>>>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>>>>> subscription preferences:
>>>>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>>>>> 
>>>>>>> _______________________________________________
>>>>>>> use-livecode mailing list
>>>>>>> use-livecode at lists.runrev.com
>>>>>>> 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
>>>>> use-livecode at lists.runrev.com
>>>>> Please visit this url to subscribe, unsubscribe and manage your
>>>> subscription preferences:
>>>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>> 
>>>> _______________________________________________
>>>> use-livecode mailing list
>>>> use-livecode at lists.runrev.com
>>>> 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
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode