parameterized query with wildcard

Mark Waddingham mark at livecode.com
Thu Jul 30 15:08:07 EDT 2015 

Injection attacks are caused by a lack of validation of the inputs used to construct a query - they are independent of any transport mechanism.

Even fronting db access with a web service will cause a vector for injection attacks if the web service is constructing query strings without validating or escaping inputs which are put directly into said strings.

As previously mentioned on this thread, placeholders mitigate the problem because DBs treat them as parameters to a query, rather than building a query with their content concatenated into the query string.

Mark.

Sent from my iPhone

> On 30 Jul 2015, at 19:57, Ralph DiMola <rdimola at evergreeninfo.net> wrote:
> 
> Peter,
> 
> If you using an SSL connection to the DB is there still "SQL injection" risks?
> 
> 3...2...1... and Mark Wieder says "Use a web service"
> 
> Then I will +1 it. I have almost completely moved to the https web service model for remote DB access.
> 
> Ralph DiMola
> IT Director
> Evergreen Information Services
> rdimola at evergreeninfo.net
> 
> 
> -----Original Message-----
> From: use-livecode [mailto:use-livecode-bounces at lists.runrev.com] On Behalf Of Peter Haworth
> Sent: Thursday, July 30, 2015 2:47 PM
> To: How to use LiveCode
> Subject: Re: parameterized query with wildcard
> 
> I feel I should point out that you are leaving yourself wide open to SQL injection attacks by not using the placeholder method of passing data to SQL statements.  Not enough space to detail how that works here but just Google "SQL injection" on the web to see a sample of the really bad things that can happen.
> 
> When you use placeholders, the SQL statement and the values in the placeholder variables are sent to the database separately which gives you full protection against SQL injection attacks.
> 
>> On Wed, Jul 29, 2015 at 10:02 AM PystCat <pystcat at gmail.com> wrote:
>> 
>> 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
>> 
>> _______________________________________________
>> 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

More information about the use-livecode mailing list