parameterized query with wildcard

Thu Jul 30 15:12:40 EDT 2015

Good questions Ralph, not sure I have definitive answers.  I do know that
even if your queries are executed by PHP scripts on your sever, there are
ways for SQL injection attacks to occur, although it's much more
complicated.  There's a good Wikipedia article on this at
https://en.wikipedia.org/wiki/SQL_injection.  There are some really scary
examples of attacks that have occurred using this method at the end of the
article.  There are a lot of bad people out there!

For my own peace of mind, I automatically use placeholders these days so I
don't have to concern myself whether I'm already protected against attacks
or not

Just saw Mark's reply while writing this which I think validates the use of
placeholders.

On Thu, Jul 30, 2015 at 11:56 AM 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