1

I've got a select query in a Google Sheet, for the most part it works. It's selecting a bunch of rows where a column equals a value in another cell. For example:

WHERE G='" & K7 & "' AND Q is not Null order by Q Asc limit 3

I've just encountered a problem where if K7 which is a text value contains a ' in the cell the query doesn't work. What do I need to include to ensure the query still works if it contains these values?

demazter
  • 35
  • 7
  • Use a parameterized query. This is what they exist to prevent. The malicious exploitation of this type of bug is known as SQL injection. – Bacon Bits Apr 24 '21 at 21:19
  • Thank you, could you explain how to do this? – demazter Apr 24 '21 at 21:29
  • K7 is imported from another source and is names. The value of K7 will be different every time. But other cells may have other values that also contain a ' so I need a method to deal with this. – demazter Apr 24 '21 at 21:44
  • That will depend entirely upon your RDBMS and your programmatic interface. If, on the other hand, you're using [the Google Sheets query function](https://support.google.com/docs/answer/3093343?hl=en), then [this answer](https://stackoverflow.com/a/42506783/696808) Is probably what you're actually looking for rather than a general SQL answer. – Bacon Bits Apr 24 '21 at 21:55

1 Answers1

1

try simple displacement:

=INDEX(SUBSTITUTE(QUERY(SUBSTITUTE({G:G, Q:Q}, "'", "♥"), 
 "where Col1 = '"&SUBSTITUTE(K7, "'", "♥")&"' 
    and Col2 is not null 
  order by Col2 asc 
  limit 3"), "♥", "'"))
player0
  • 124,011
  • 12
  • 67
  • 124