parameterized query with wildcard

[Peter Haworth]

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