4 ms·
Yes, SQL has flaws, and the article forgot to mention one of them: you need to build a string to build an SQL query, rather than a more structured object, leadi
by Flimm 6y ago
Yes, SQL has flaws, and the article forgot to mention one of them: you need to build a string to build an SQL query, rather than a more structured object, leading to flaws like SQL injection vulnerabilities, and difficulties adjusting the query.
Let's say you're building a CRUD app with search and filtering capabilities. Unless you are using an ORM (which has problems of its own), you might be tempted to build the SQL query string like this:
conditions = " AND ".join(filter_key + " = '" + filter_value + "'" for kilter_key, filter_value in filter.items())
order_by = column_name + " DESC"
query = "SELECT col1 FROM tablename WHERE " + conditions + " ORDER BY " + order_by
But this has multiple SQL injection vulnerabilities. Doing it correctly is not just a matter of using SQL parameters, because column names need to be escaped differently than string literals. Linters can't distinguish between correctly escaped queries and incorrectly escape queries in non-trivial cases. Also, the query will throw a syntax error if the number of filters is zero, since you can't have an empty WHERE clause.
I don't think a new query language solves this problem.
- maweki 6y agoThis problem would arise with every DSL. And it has been solved by using embedded DSLs. jOOQ or SQLAlchemy look like SQL (and you don't even have to squint your eyes very much) and solve the problems you mention.
- Flimm 6y agojOOQ and SQLAlchemy are libraries for Java and Python respectively. You can't take your knowledge of those libraries and use them in a different programming language. And you still need to know SQL well in order to do any debugging, because the database is still receiving an SQL query string. What I am wishing for is for the language to be more like JSON, something that matches closely to commonly found structures in programming languanges (like lists, objects, numbers, strings and booleans), and that the database can support natively.
- maweki 6y ago> jOOQ and SQLAlchemy are libraries While it is true, they are specifically embedded DSLs, which is exactly what you said you needed. Having a (query) language (a DSL) that is not composed by string concatenation. Embedded DSL solve exactly that problem and they are usually just translations from some DSL into concrete syntax in some programming language, ideally supporting type correctness and preventing syntax errors, etc.. And what you "wish for" is, again, just an embedded DSL, you only wish for you constructors/functions to be native instead of some import/module.
- dsego 6y ago> the article forgot to mention one of them: you need to build a string to build an SQL query IMO this is in the article mentioned as 'poor system cohesion': > poor system cohesion — SQL does not integrate well enough with application languages and protocols ---- > I don't think a new query language solves this problem. LINQ ?
- jimbokun 6y agoIsn't the solution a "query builder" pattern for your preferred programming language, that generates a SQL string and sends that to the database?
- ilikehurdles 6y agoYou're right that a new query language doesn't solve this. Datalog is over 40 years old, after all.
- maweki 6y agoAnd we're still researching how aggregation should work. While Datalog is really cool and my PhD research is based on it, the fixpoint semantics really do not work well with aggregation, grouping, etc.
- bmh100 6y agoI think this is an unfair comparison. Constructing SQL query strings is risky, because it is essentially dynamically constructing and evaluating code from user input. However, there is no reason why one can't use parameters (like you mention) or even call stored procedures.