Re: parameterized query with wildcard
Yes it does. If you use the placeholder method (I am not really sure what to call it at this point) then sqlYoga sanitizes the strings for you. I've inserted records with any number of characters using this method without any problems reading in or out of the database. I'm not sure if a direct query like address = '\\bobscomputer\scans' is sanitized. For the sake of standardization I always use the placeholder method. Bob S On Jul 28, 2015, at 09:17 , Andrew Kluthe and...@ctech.me wrote: Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking extra SQL into your search box like an injection style attack, or does it just plop whatever you give in there no questions asked? Just curious. I have always been spoiled by SQLYoga or rolled my DB interfaces up into API servers of some kind. On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy d...@applicationinsight.com wrote: Mike, assuming you are searching the db with parameter pSearchTerm, try something like this: put % pSearchTerm % into tSearchTerm put SELECT * FROM foo WHERE bar LIKE :1 into tQuery get revDataFromQuery(tab, return, sDBID, tQuery, tSearchTerm) - The difference between genius and stupidity is; genius has its limits. - Albert Einstein -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
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 pyst...@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 p...@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 pyst...@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 mikeker...@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 pyst...@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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@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
RE: parameterized query with wildcard
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 rdim...@evergreeninfo.net -Original Message- From: use-livecode [mailto:use-livecode-boun...@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 pyst...@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 p...@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 pyst...@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 mikeker...@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 pyst...@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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http
Re: parameterized query with wildcard
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 rdim...@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 rdim...@evergreeninfo.net -Original Message- From: use-livecode [mailto:use-livecode-boun...@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 pyst...@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 p...@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 pyst...@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 mikeker...@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 pyst...@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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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
Re: parameterized query with wildcard
If you use the placeholder method, there is no need to sanitize the strings, that's the point of using it. Although I guess it depends on what you mean by sanitize. On Thu, Jul 30, 2015 at 8:11 AM Bob Sneidar bobsnei...@iotecdigital.com wrote: Yes it does. If you use the placeholder method (I am not really sure what to call it at this point) then sqlYoga sanitizes the strings for you. I've inserted records with any number of characters using this method without any problems reading in or out of the database. I'm not sure if a direct query like address = '\\bobscomputer\scans' is sanitized. For the sake of standardization I always use the placeholder method. Bob S On Jul 28, 2015, at 09:17 , Andrew Kluthe and...@ctech.me wrote: Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking extra SQL into your search box like an injection style attack, or does it just plop whatever you give in there no questions asked? Just curious. I have always been spoiled by SQLYoga or rolled my DB interfaces up into API servers of some kind. On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy d...@applicationinsight.com wrote: Mike, assuming you are searching the db with parameter pSearchTerm, try something like this: put % pSearchTerm % into tSearchTerm put SELECT * FROM foo WHERE bar LIKE :1 into tQuery get revDataFromQuery(tab, return, sDBID, tQuery, tSearchTerm) - The difference between genius and stupidity is; genius has its limits. - Albert Einstein -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
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 rdim...@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 rdim...@evergreeninfo.net -Original Message- From: use-livecode [mailto:use-livecode-boun...@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 pyst...@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 p...@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 pyst...@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 mikeker...@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 pyst...@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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
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 p...@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 pyst...@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 mikeker...@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 pyst...@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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
Re: parameterized query with wildcard
If you are specifying a literal value with LIKE, then you need the single quotes or you will get an error. If you are using a parameter variable containing the literal, then no single quotes needed. Including the :1 in quotes makes the query look for a string containing :1, not the contents of the variable referred to by :1. On Wed, Jul 29, 2015 at 6:33 AM Mike Kerner mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
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 pyst...@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 mikeker...@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 pyst...@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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
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 mikeker...@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 pyst...@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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
How odd. I am thinking now, that because I am passing these query arguements to sqlYoga it is doing the macro replacement instead of SQL. Now that I think of it, I have never used this in a direct SQL query. I am not even sure how to construct it. Is this a web server convention? I cannot see how you would inject the values in a direct query. Bob S On Jul 29, 2015, at 09:16 , Peter Haworth p...@lcsql.com wrote: If you are specifying a literal value with LIKE, then you need the single quotes or you will get an error. If you are using a parameter variable containing the literal, then no single quotes needed. Including the :1 in quotes makes the query look for a string containing :1, not the contents of the variable referred to by :1. On Wed, Jul 29, 2015 at 6:33 AM Mike Kerner mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
Turns out it is done in the system settings under keyboard in case anyone else needs to do the same. Bob S On Jul 29, 2015, at 15:06 , Bob Sneidar bobsnei...@iotecdigital.commailto:bobsnei...@iotecdigital.com wrote: I should also mention that my mail program *DOES* substitute plain quotes for smart ones. I am disabling that “feature” now. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
Okay I see my confusion. I can use a statement like: select * from customers where customername LIKE '%int%’ This works. It seems however that when using parameter substitution it does not. In sqlYoga I can use: put sqlquery_createObject(“customers”) into qCustomerObject put “customer name LIKE ‘%:1%’” into tConditions put “int” into aBindings [1] sqlquery_set qCustomerObject, “conditions”, tConditions sqlquery_set qCustomerObject, “condition bindings”, aBindings sqlquery_retrieveAsArray qCustomerObject, aCustomerData This also works. I should also mention that my mail program *DOES* substitute plain quotes for smart ones. I am disabling that “feature” now. Bob S On Jul 29, 2015, at 14:35 , Bob Sneidar bobsnei...@iotecdigital.com wrote: How odd. I am thinking now, that because I am passing these query arguements to sqlYoga it is doing the macro replacement instead of SQL. Now that I think of it, I have never used this in a direct SQL query. I am not even sure how to construct it. Is this a web server convention? I cannot see how you would inject the values in a direct query. Bob S ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
If you copied and pasted it may be that the small quotes are not the right characters. I have used this query successfully myself so I may have mistyped something. Bob S On Jul 29, 2015, at 06:31 , Mike Kerner mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
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 pyst...@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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
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 mikeker...@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 mikeker...@roadrunner.com wrote: Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
Nope. That doesn't work, Bob. That returns nothing. On Tue, Jul 28, 2015 at 7:23 PM, Bob Sneidar bobsnei...@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 mikeker...@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
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 mikeker...@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
parameterized query with wildcard
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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
Mike, assuming you are searching the db with parameter pSearchTerm, try something like this: put % pSearchTerm % into tSearchTerm put SELECT * FROM foo WHERE bar LIKE :1 into tQuery get revDataFromQuery(tab, return, sDBID, tQuery, tSearchTerm) - The difference between genius and stupidity is; genius has its limits. - Albert Einstein -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
Should have read, *proper escaping*. On Tue, Jul 28, 2015 at 11:17 AM Andrew Kluthe and...@ctech.me wrote: Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking extra SQL into your search box like an injection style attack, or does it just plop whatever you give in there no questions asked? Just curious. I have always been spoiled by SQLYoga or rolled my DB interfaces up into API servers of some kind. On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy d...@applicationinsight.com wrote: Mike, assuming you are searching the db with parameter pSearchTerm, try something like this: put % pSearchTerm % into tSearchTerm put SELECT * FROM foo WHERE bar LIKE :1 into tQuery get revDataFromQuery(tab, return, sDBID, tQuery, tSearchTerm) - The difference between genius and stupidity is; genius has its limits. - Albert Einstein -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking extra SQL into your search box like an injection style attack, or does it just plop whatever you give in there no questions asked? Just curious. I have always been spoiled by SQLYoga or rolled my DB interfaces up into API servers of some kind. On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy d...@applicationinsight.com wrote: Mike, assuming you are searching the db with parameter pSearchTerm, try something like this: put % pSearchTerm % into tSearchTerm put SELECT * FROM foo WHERE bar LIKE :1 into tQuery get revDataFromQuery(tab, return, sDBID, tQuery, tSearchTerm) - The difference between genius and stupidity is; genius has its limits. - Albert Einstein -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
Dave, I take that back - I must have had a typo the first time I tried it. Appending the wildcards to the search parameter does work. On Tue, Jul 28, 2015 at 12:28 PM, Mike Kerner mikeker...@roadrunner.com wrote: Dave, sorry, I thought I mentioned trying that. It does not work. Andrew, yes, if you use a parameterized query, you do not have to escape/sanitize your parameters. If you append them to build a query, you do. On Tue, Jul 28, 2015 at 12:18 PM, Andrew Kluthe and...@ctech.me wrote: Should have read, *proper escaping*. On Tue, Jul 28, 2015 at 11:17 AM Andrew Kluthe and...@ctech.me wrote: Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking extra SQL into your search box like an injection style attack, or does it just plop whatever you give in there no questions asked? Just curious. I have always been spoiled by SQLYoga or rolled my DB interfaces up into API servers of some kind. On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy d...@applicationinsight.com wrote: Mike, assuming you are searching the db with parameter pSearchTerm, try something like this: put % pSearchTerm % into tSearchTerm put SELECT * FROM foo WHERE bar LIKE :1 into tQuery get revDataFromQuery(tab, return, sDBID, tQuery, tSearchTerm) - The difference between genius and stupidity is; genius has its limits. - Albert Einstein -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: parameterized query with wildcard
Dave, sorry, I thought I mentioned trying that. It does not work. Andrew, yes, if you use a parameterized query, you do not have to escape/sanitize your parameters. If you append them to build a query, you do. On Tue, Jul 28, 2015 at 12:18 PM, Andrew Kluthe and...@ctech.me wrote: Should have read, *proper escaping*. On Tue, Jul 28, 2015 at 11:17 AM Andrew Kluthe and...@ctech.me wrote: Does revDataFromQuery do any sanitizing/proper to prevent me from sneaking extra SQL into your search box like an injection style attack, or does it just plop whatever you give in there no questions asked? Just curious. I have always been spoiled by SQLYoga or rolled my DB interfaces up into API servers of some kind. On Tue, Jul 28, 2015 at 11:09 AM Dave Kilroy d...@applicationinsight.com wrote: Mike, assuming you are searching the db with parameter pSearchTerm, try something like this: put % pSearchTerm % into tSearchTerm put SELECT * FROM foo WHERE bar LIKE :1 into tQuery get revDataFromQuery(tab, return, sDBID, tQuery, tSearchTerm) - The difference between genius and stupidity is; genius has its limits. - Albert Einstein -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/parameterized-query-with-wildcard-tp4694407p4694419.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
