parameterized query with wildcard

Thu Jul 30 14:57:05 EDT 2015

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