0

I have to take over old colleague's code and trying to understand this one SQL statement like below:

SELECT * FROM my_table WHERE date_key = '{0}'

Column date_keycontains int values such as 20220712, 20220120, etc.

The first guess is that SELECT statement filters for rows with 0 value in column date_key. However, when running that line of code, I receive this error :

SQL Error [100038] [22018]: Numeric value '{0}' is not recognized

What exactly does that line of code do?

Tristan Tran
  • 1,351
  • 1
  • 10
  • 36
  • 2
    Please add a bigger part of the code not just 1 SQL statement. (I am guessing: Probably `{0}` is being replaced by the value you need?) – Luuk Jul 25 '22 at 06:13
  • 1
    It also looks like it is susceptible to SQL injection. – Mark Rotteveel Jul 25 '22 at 06:19
  • What's the host language? Is the statement part of a prepared query (or similar)? – outis Jul 25 '22 at 06:34
  • @outis The SQL statement is called in an ```cursor.execute``` statement with a ```Snowflake Connector``` in Python (ie. ```import snowflake.connector```) – Tristan Tran Jul 25 '22 at 06:39
  • Please [edit] clarifications into to the question (as explained in the [site guidelines](//stackoverflow.com/help/how-to-ask) and [sample code guidelines](//stackoverflow.com/help/mcve)), rather than leaving [comments](//stackoverflow.com/help/privileges/comment). For one thing, a question should be understandable without reading comments. For another, SO is a Q&A site, not a forum, and comments aren't intended (nor are they well suited) for discussions. – outis Jul 25 '22 at 07:29
  • 1
    This question probably needs a [mcve]; currently, it's not complete, as the answer hinges on the python side of the code, possibly [string formatting](https://docs.python.org/2/library/stdtypes.html#str.format) (particularly as [`Cursor.execute`](https://docs.snowflake.com/en/user-guide/python-connector-api.html#execute) doesn't support any [placeholders](https://docs.snowflake.com/en/user-guide/python-connector-example.html#label-python-connector-binding-data) matching the `{0}` format, and given the single-quotes around the placeholder). – outis Jul 25 '22 at 07:41
  • @outis You are right. Now that I look at the Python side, it does seems that is a placeholder to be fed with a string. Closing this question. – Tristan Tran Jul 25 '22 at 07:52
  • 1
    @TristanTran: take heed of Mark Rotteveel's comment. There likely is an injection vulnerability, which is completely unnecessary, especially given that Snowflake supports parameters (what it terms ["bindings"](https://docs.snowflake.com/en/user-guide/python-connector-example.html#label-python-connector-binding-data)). – outis Jul 25 '22 at 07:56

1 Answers1

1

That looks like a placeholder, replaced with an actual value in code when calling the query.

See similar What is {0},{1},{2},{3} in the SQL query

David
  • 146
  • 1
  • 9