6 ms·

Previous discussion from 2015: https://news.ycombinator.com/item?id=9178773 https://news.ycombinator.com/item?id=9178773

Twisell 6y ago
The top comments of this old thread are retrospectively hilarious because sone frontend guys argues about wether SQL is not easily composable and exchanges trick about how you can securely and dynamically concatenate SQL statements. But nowhere to be found is the concept of prepared statements that would gracefully help then write nicer code. https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html And that is a lightweight in-code solution for those who are repelled by database procedures which is yet another way to solve theirs issue with SQL.
- amenod 6y ago
  Correct me if I'm wrong, but I think you missed the point of the discussion? It is not connected to frontend at all. It talks about the difference between using an ORM versus constructing SQL statements directly, as strings. In that light I don't think `PREPARE` and stored procedures would help much (or at all). I also don't find them hilarious, though they do represent very extreme views on the subject (imho) while to me it is essentially a tradeoff (I use both direct SQL and ORMs, depending on the needs of the project).
  - Twisell 6y ago
    My point is precisely that they compare two subpar approach of SQL while prepared or stored procedure are the real contender here. And as far as I understand depending on your use case prepared or stored is better. Prepared mean less code base inside the db and still more composability with less sql injection risks. Stored add even more composability and security but at the cost of managing code inside the db.
- aidos 6y ago
  I was involved in those original discussions and it was just the standard “how to manipulate sql” discussion. Prepared statements don’t help you here, right? My understanding is that they’re purely for performance as the parser doesn’t need to be rerun each time. You need to recreate them for every dB session.
  - richardw 6y ago
    No sql injection, can set permissions for each proc, isolates app from DB implementation so you don’t have to redeploy app. Eg you can migrate older data to an archive table and query it on certain circumstances. App doesn’t need to know.
    - aidos 6y ago
      That’s stored ptocedures you’re talking about - I think. We’re talking about prepared statements.
      - richardw 6y ago
        Thanks! That was silly
      - Twisell 6y ago
        Please proceed to explain why in your opinion prepared statement don't help preventing SQL injection. I might be wrong and I'm always happy to learn new things but give me some arguments instead of downvoting (as i guess it's you).
  - Twisell 6y ago
    I'm not familiar enought with the execution context, i could be wrong and i totally get that theses are examples while final code would probably include escaping checks. But that being said some of the proposed solutions could probably lead to a little bobby table situation... https://xkcd.com/327/ https://xkcd.com/327/

Twisell 6y ago

The top comments of this old thread are retrospectively hilarious because sone frontend guys argues about wether SQL is not easily composable and exchanges trick about how you can securely and dynamically concatenate SQL statements. But nowhere to be found is the concept of prepared statements that would gracefully help then write nicer code. https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html And that is a lightweight in-code solution for those who are repelled by database procedures which is yet another way to solve theirs issue with SQL.

amenod 6y ago
Correct me if I'm wrong, but I think you missed the point of the discussion? It is not connected to frontend at all. It talks about the difference between using an ORM versus constructing SQL statements directly, as strings. In that light I don't think `PREPARE` and stored procedures would help much (or at all). I also don't find them hilarious, though they do represent very extreme views on the subject (imho) while to me it is essentially a tradeoff (I use both direct SQL and ORMs, depending on the needs of the project).
- Twisell 6y ago
  My point is precisely that they compare two subpar approach of SQL while prepared or stored procedure are the real contender here. And as far as I understand depending on your use case prepared or stored is better. Prepared mean less code base inside the db and still more composability with less sql injection risks. Stored add even more composability and security but at the cost of managing code inside the db.
aidos 6y ago
I was involved in those original discussions and it was just the standard “how to manipulate sql” discussion. Prepared statements don’t help you here, right? My understanding is that they’re purely for performance as the parser doesn’t need to be rerun each time. You need to recreate them for every dB session.
- richardw 6y ago
  No sql injection, can set permissions for each proc, isolates app from DB implementation so you don’t have to redeploy app. Eg you can migrate older data to an archive table and query it on certain circumstances. App doesn’t need to know.
  - aidos 6y ago
    That’s stored ptocedures you’re talking about - I think. We’re talking about prepared statements.
    - richardw 6y ago
      Thanks! That was silly
    - Twisell 6y ago
      Please proceed to explain why in your opinion prepared statement don't help preventing SQL injection. I might be wrong and I'm always happy to learn new things but give me some arguments instead of downvoting (as i guess it's you).
- Twisell 6y ago
  I'm not familiar enought with the execution context, i could be wrong and i totally get that theses are examples while final code would probably include escaping checks. But that being said some of the proposed solutions could probably lead to a little bobby table situation... https://xkcd.com/327/ https://xkcd.com/327/