4 ms·

I think you underestimate how much stuff relies on string concatenation or string interpolation to build SQL statements that are also case-insensitive by nature

by eddieh 10y ago

I think you underestimate how much stuff relies on string concatenation or string interpolation to build SQL statements that are also case-insensitive by nature. Yes, people should probably use an ORM and yes they should properly quote input, but so much code exists that doesn't. Even a strongly typed host language doesn't preclude stringly typed SQL.

elygre 10y ago
No, people shouldn't quote input. They should bind input.
- eddieh 10y ago
  Please explain! I assume you mean bind to a type, but how does that help when you build a SQL statement? Surely whatever type you use has a 'toString' (or language equivalent) that is explicitly or implicitly used with string concatenation or string interpolation.
  - DougWebb 10y ago
    You build your SQL statement using named parameters for the values you're going to pass in, and you use your database's client API to create a command and bind parameter values to it. Then you ask the API to execute the command. The binding step accepts values in your language's native data types, and handles conversions to the database's data types for you in a safe manner.
    - eddieh 10y ago
      Gotcha. I didn't know what this was called. Basically it is what I meant, but a good portion of deployed code doesn't use this from my experience. Personally I'd use an ORM, but that's just me.
  - 0x0 10y ago
    No, you put in placeholders like ? in the query string, and supply replacement parameters out-of-band separately. See for example the bindValue api in the PHP DBAL API: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html http://docs.doctrine-project.org/projects/doctrine-dbal/en/l... This way, possibly-user-supplied values are never mixed with the query string, and you don't have to worry about quoting.
    - eddieh 10y ago
      Haha, I didn't know that was what that was called. It has been a few years since I did database driven development.

by eddieh 10y ago

elygre 10y ago

No, people shouldn't quote input. They should bind input.

eddieh 10y ago
Please explain! I assume you mean bind to a type, but how does that help when you build a SQL statement? Surely whatever type you use has a 'toString' (or language equivalent) that is explicitly or implicitly used with string concatenation or string interpolation.
- DougWebb 10y ago
  You build your SQL statement using named parameters for the values you're going to pass in, and you use your database's client API to create a command and bind parameter values to it. Then you ask the API to execute the command. The binding step accepts values in your language's native data types, and handles conversions to the database's data types for you in a safe manner.
  - eddieh 10y ago
    Gotcha. I didn't know what this was called. Basically it is what I meant, but a good portion of deployed code doesn't use this from my experience. Personally I'd use an ORM, but that's just me.
- 0x0 10y ago
  No, you put in placeholders like ? in the query string, and supply replacement parameters out-of-band separately. See for example the bindValue api in the PHP DBAL API: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html http://docs.doctrine-project.org/projects/doctrine-dbal/en/l... This way, possibly-user-supplied values are never mixed with the query string, and you don't have to worry about quoting.
  - eddieh 10y ago
    Haha, I didn't know that was what that was called. It has been a few years since I did database driven development.