3 ms·

The minimum enhancement I want for SQL is a version where no literal values are allowed in queries, as this would completely preclude SQL injection :) To make

The minimum enhancement I want for SQL is a version where no literal values are allowed in queries, as this would completely preclude SQL injection :) To make that more tolerable for query planning purposes, there would have to be two types of query parameters: compile-time and run-time. Next up: why not allow query clauses to come in any order? `SELECT .. FROM .. WHERE ..;` or `FROM .. SELECT .. WHERE;` and so on. Since the query parser/planner has to see the whole thing anyways. The parser/planner can't begin coding at `SELECT`, or at `WHERE`, since there might be a `GROUP BY`, or an `ORDER BY` that affect the whole query plan, so all these clauses might as well come in any order. Wanna put `HAVING` first? Sure, why not. It's probably best to insist that table sources all come together rather than be all over, but I think even that doesn't have to be so. Also, I'd like an out-of-band mechanism for expressing query planner hints. This would be a separate string or object passed along with the query, and which does things like: identify a table source to use as the outer-most table for the query plan, for each of some or all table sources identify an index to use or temp index to create, for each of some or all joins pick a join strategy, etc. Table sources would have to be addressed as {<CTE_name>, <table_source_name>}, naturally. Such a thing should also allow one to specify indices that should be created on CTEs.

klysm 4y ago
Not sure that’s worth it? It’s not a difficult software engineering problem to prevent SQL injection categorically.
- staticassertion 4y ago
  A lot of safety/security isn't hard, people just don't do it if it isn't forced on them/ easy.
  - cryptonector 4y ago
    This. If SQL injection was infeasible because the language had no literals, then there would be no SQL injection. Given that SQL injection is possible because the language does have literals, we should and do in fact still have SQL injection issues.
    - klysm 4y ago
      The fact that SQL has literals is not a problem. It’s true that eliminating literals would prevent SQL injection but it’s an absurd solution to the problem. How should we write queries by hand? What about things that actually should be constant (e.g where col is null)?
jiggawatts 4y ago
> this would completely preclude SQL injection :) No it wouldn't! I regularly see web apps with URLs like this: someapp/reports/foo?start=1000&end=2000&sort=DESC There are two fixes for this that mostly work: 1) Use a binary-only format that has no string representation at all. Instead, queries must be built up using a fluent builder style, or similar API surface. This is fine for "apps", but is less useful for ad-hoc queries obviously. 2) Use something similar to what web app frameworks do when generating HTML. Instead of accepting naked 'string' types, only accept something like an 'IEncodedHtml' type that automatically converts from strings, but escapes them in the process. Passing in a raw string as SQL would require some sort of scary-looking function call that is easy to grep in a codebase. E.g.: "QueryBuilder.UnsafeConvertTrustedString(...)" to really drive the point home. And then of course, IDEs and build tools would need to emit warnings or outright errors if HTTP inputs are directly passed to this function as-is. Having said that, nothing will ever be sufficient. Developers start sentences with: "I just want to...", which is basically to say that they don't want to have to "deal" with security and just get it "working". Another GIANT mistake I see in most (all?) database clients is that there is no distinction between needing a read-write connection or a read-only connection. Making read-only the default would have huge benefits, such as: 1) Making all apps automatically gain the ability to do read-scale-out when using systems such as SQL Server AlwaysOn Availability Groups with readable secondaries. Right now this takes a major application code change to have any benefit, which means "nobody" does it. I've been literally begging and pleading with developers to just add a second connection string with "ApplicationIntent=ReadOnly" in it for report generation, and NOBODY has done it. Not one developer. 2) Security! If you don't need your app, page, or specific query to write, then don't enable the write privilege! Again, the default here is super dangerous, but every developer does it because it's the easy happy path. I'd be shocked if even 1% of the web applications in the wild switch to a read-only account or connection string, because it is so fiddly in current systems. Even with all of the above, databases won't be perfectly secure against injection. The latest SQL injection attacks I've seen pen testers use is to add "DELAY" commands to exfiltrate data one bit at a time. This works similarly to micro-architectural side-channel attacks such as the famous Spectre and Meltdown vulnerabilities in Intel. Even if explicit injection isn't possible, timing attacks are often still possible if ad-hoc query generation is exposed. Security is hard.

klysm 4y ago

Not sure that’s worth it? It’s not a difficult software engineering problem to prevent SQL injection categorically.

staticassertion 4y ago
A lot of safety/security isn't hard, people just don't do it if it isn't forced on them/ easy.
- cryptonector 4y ago
  This. If SQL injection was infeasible because the language had no literals, then there would be no SQL injection. Given that SQL injection is possible because the language does have literals, we should and do in fact still have SQL injection issues.
  - klysm 4y ago
    The fact that SQL has literals is not a problem. It’s true that eliminating literals would prevent SQL injection but it’s an absurd solution to the problem. How should we write queries by hand? What about things that actually should be constant (e.g where col is null)?

jiggawatts 4y ago

> this would completely preclude SQL injection :) No it wouldn't! I regularly see web apps with URLs like this: someapp/reports/foo?start=1000&end=2000&sort=DESC There are two fixes for this that mostly work: 1) Use a binary-only format that has no string representation at all. Instead, queries must be built up using a fluent builder style, or similar API surface. This is fine for "apps", but is less useful for ad-hoc queries obviously. 2) Use something similar to what web app frameworks do when generating HTML. Instead of accepting naked 'string' types, only accept something like an 'IEncodedHtml' type that automatically converts from strings, but escapes them in the process. Passing in a raw string as SQL would require some sort of scary-looking function call that is easy to grep in a codebase. E.g.: "QueryBuilder.UnsafeConvertTrustedString(...)" to really drive the point home. And then of course, IDEs and build tools would need to emit warnings or outright errors if HTTP inputs are directly passed to this function as-is. Having said that, nothing will ever be sufficient. Developers start sentences with: "I just want to...", which is basically to say that they don't want to have to "deal" with security and just get it "working". Another GIANT mistake I see in most (all?) database clients is that there is no distinction between needing a read-write connection or a read-only connection. Making read-only the default would have huge benefits, such as: 1) Making all apps automatically gain the ability to do read-scale-out when using systems such as SQL Server AlwaysOn Availability Groups with readable secondaries. Right now this takes a major application code change to have any benefit, which means "nobody" does it. I've been literally begging and pleading with developers to just add a second connection string with "ApplicationIntent=ReadOnly" in it for report generation, and NOBODY has done it. Not one developer. 2) Security! If you don't need your app, page, or specific query to write, then don't enable the write privilege! Again, the default here is super dangerous, but every developer does it because it's the easy happy path. I'd be shocked if even 1% of the web applications in the wild switch to a read-only account or connection string, because it is so fiddly in current systems. Even with all of the above, databases won't be perfectly secure against injection. The latest SQL injection attacks I've seen pen testers use is to add "DELAY" commands to exfiltrate data one bit at a time. This works similarly to micro-architectural side-channel attacks such as the famous Spectre and Meltdown vulnerabilities in Intel. Even if explicit injection isn't possible, timing attacks are often still possible if ad-hoc query generation is exposed. Security is hard.