11 ms·

Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UP

Let's say you have a product search screen in your application. There's a text field for filtering on product title (WHERE title LIKE), one for filtering on UPC (WHERE upc LIKE), a couple range filters for min/max prices (WHERE price <= and/or WHERE price >=), and then on the results screen the user sort on a few different columns (ORDER BY) as well as paginate and set number of results per-page (LIMIT + OFFSET). How exactly are you going to just write SQL if the actual query statement needs to change based on the user input? Are you going to hardcode every possible permutation, or are you going to use a query builder? Or are there some advanced Postgres features that I'm not aware of that would allow all these combinations in some kind of prepared statement? edit: The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments. Your ad-hoc query builder implementations with SQL injection vulnerabilities are not relevant to what I'm asking.

mbreese 12y ago
You might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...) sql = 'SELECT * FROM products' args = [] if title: sql += ' WHERE title LIKE %?%' args.append(title) if upc: if args: sql += ' AND' else: sql += ' WHERE' sql += ' upc LIKE %?%' args.append(upc) if price_min: if args: sql += ' AND' else: sql += ' WHERE' sql += ' price >= ?' args.append(price_min) if price_max: if args: sql += ' AND' else: sql += ' WHERE' sql += ' price <= ?' args.append(price_max) if order_by_name: sql += ' ORDER BY name' elif order_by_price_low: sql += ' ORDER BY price' elif order_by_price_high: sql += ' ORDER BY price DESC' conn.query(sql, args)
- ad_hominem 12y ago
  This is a query builder. If you're using your programming language to dynamically assemble the SQL statement fragments at runtime, then you're using a query builder regardless of if it is a library with a fancy DSL that assembles an in-memory SQL AST or some kind of ad-hoc string concatenation you rolled yourself like this. The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments.
  - mbreese 12y ago
    Yes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.
    - ad_hominem 12y ago
      Disregarding my edit and how your query builder is irrelevant to my question anyway, if I change the requirement to allow the user to sort by multiple columns instead of one, that case statement is going to either combinatorially explode or you'll also have to add some ad-hoc SQL escaping to guard against SQL injection since you can't use bound parameters in ORDER BY clauses. It's easy to see how libraries like Sequel get invented to help people do this, and I don't really see what the problem is when the DSL stays close to SQL semantics.
      - mbreese 12y ago
        Dealing with multiple ORDER BY clauses isn't any more difficult, but you'd have to figure out how to get the user input into some kind of list. That will require validation too... Just using an ORM doesn't let you ignore input validation. You wouldn't trust a user to use their own column names in a WHERE clause anymore than you'd trust them in an ORDER BY clause. Trying to differentiate between a DSL that interfaces with SQL and SQL is nonsensical. SQL is the original DSL.
hammerandtongs 12y ago
You've formulated the composibility reasonably in that sentence but it's important to remember that is not the only problem the world faces in data manipulation. I think the irony is that much of the problems of sql are related to a lack of tooling. The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor. Essentially we lack great *.sql editing, ide, macro, refactoring because of the lack of focus on sql itself. edit: as an aside, string concatenation in the language of choice is its own road to hell.
- rdtsc 12y ago
  > Essentially we lack great *.sql editing, ide, macro, refactoring because of the lack of focus on sql itself. Except that my other tools are just an editor like emacs and makefiles. So now to be productive I have to have and ide for sql, learn sql, learn macros. Interesting that SQL came first. If it was so easy and obvious to use it we would not have seen any ORMs by now. But every other project that uses SQL databases ends up with some kind of ORM. One of the reason is probably because there is an impedance mismatch. Code is already written dealing with objects, functions, pointers, data. But when it talks to the database it has to talk via a command line string interface so to speak. There is another type of impedance mismatch and that is learning a new language. One can argue SQL is there to keep & manipulate the most precious things you have -- data, so one should spend time learning it well. But the other point is, SQL is there to keep & manipulate the most precious things you -- data, so don't mess with it if you don't know SQL very well. Let someone else learn it (the author of the ORM). I am not arguing for one point or the other, just kind saying how it is interesting how we have been going in a circle the last 15 years or so.
  - hammerandtongs 12y ago
    I also use emacs(evil-mode actually). An ide is not much different then a fully configured vim or emacs. sql-mode for some databases(mysql and oracle iirc) reads the DDL of the tables you are typing about, why is this not better and more general? Sql is so well established and general as a solution that not learning it is a poor choice.
moe 12y ago
How exactly are you going to "just write SQL" if the actual query statement needs to change based on the user input? How about something like this: s = Select.new s.add "WHERE title LIKE #{title}" if title s.add "WHERE price <= #{price_range}" if price_range s.add "LIMIT #{limit}" if limit s.add "OFFSET #{offset}" if offset s.add "id, blah, boo FROM products" s.execute Note how I deliberately shuffled the order and didn't bother with escaping. Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds. Why is there no ORM that works like this?
- ivanr 12y ago
  There is. MyBatis, which is a minimal ORM that aims to keep you as close to SQL as possible, has support for dynamic SQL: https://mybatis.github.io/mybatis-3/dynamic-sql.html https://mybatis.github.io/mybatis-3/dynamic-sql.html Edit [responding to moe, below]: that's a matter of taste. I prefer to have my SQL _outside_ my code. If I have to write a little XML to make it happen, so be it. Additionally, unlike your example, a strict separation of SQL and data ensures SQL injection is not possible. That's also a worthy goal.
  - moe 12y ago
    MyBatis Does MyBatis have an API similar to what I outlined above? Because the page that you link to shows a horror that I can't even begin to describe: <select id="findActiveBlogWithTitleLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> </select> SQL mixed with proprietary XML soup? Talk about combining the worst from all worlds...
    - bigpeopleareold 12y ago
      Get rid of the XML and you have a good start, I'd say.
- zo1 12y ago
  Python SQLAlchemy works a bit like this, if you use it that way: query = session.query(Products) if limit: query = query.limit(limit_val) if offset: query = query.offset(offset_val) Sort is a bit tricky, but as I'm a noob at SQL Alchemy, I had some not-so-pretty boilerplate code implemented for decorating queries with custom sorts. But essentially it boiled down to: if sort_type == SORT_REVERSE_CONST: sort_obj = sort_obj.desc() else: sort_obj = sort_obj.asc() query = query.order_by(sort_obj) And if you want to search through dynamic tables, change the initial query instantiation to something along these lines: query = session.query(retrieve_db_object(table_name)) And to execute: return query.all()
falcolas 12y ago
query = sprintf("select count(*) from data where %s = ?", column) results = sql.execute(query, filter_value); So long as you leave the "value" portion of the query as a '?' (or %s, or whatever your connector requires) and don't use direct user input for the column names, you're still safe from SQL injection.
rpedela 12y ago
That can be solved by writing a SQL function and the user input values are arguments to the function.
joshuahutt 12y ago
I would write a SQL function that takes all of those as optional parameters and includes a lot of these: WHERE (_title IS NULL OR title LIKE _title) AND (_minPrice IS NULL OR price > _minPrice) AND (_maxPrice IS NULL OR price < _maxPrice)
- ad_hominem 12y ago
  This actually answers the question, although I would imagine the ORDER BY handling will look pretty messy (CASE statement perhaps?). And I don't know what it would look like if the requirements changed to allow ordering by multiple columns with different possible sort directions... that might get back into dynamic SQL using a RETURN QUERY EXECUTE type of thing, which is basically using a query builder in your query language.
  - babuskov 12y ago
    Most SQL engines support ordering by column number. select c1, c2, c3, ... order by 2; would order by "c2"
- schrodinger 12y ago
  That ends up being very inefficient as the optimizer will come up with one mediocre plan that works for all those criteria.
bigpeopleareold 12y ago
I'll deal with the problem when I have to. However, the point is that I'd rather use SQL, it's simple as that.
wallyhs 12y ago
> Or are there some advanced Postgres features that I'm not aware of that would allow all these combinations in some kind of prepared statement? I can't speak specifically to Postgres, but, in my experience, there is nearly always a way to do what you want with bound parameters. No advanced features are required. It often results in poor performance and redundant code that is hard on the eyes. You will get what you deserve, but sometimes you don't have a choice. Here is an example of the horror: WHERE (:1 IS NULL OR :1 = item_id) AND item_num BETWEEN NVL(:2,0) AND NVL(:2,9999999999) AND item_date BETWEEN NVL(:3,'01-jan-1900') AND NVL(:3,'31-dec-4712') AND item_name LIKE NVL(:4,'%') ORDER BY CASE :5 WHEN 'NUM' THEN item_num WHEN 'DATE' THEN item_date ELSE item_name END, CASE :6 WHEN 'NUM' THEN item_num WHEN 'DATE' THEN item_date ELSE item_name END Edit: I suppose you could also parameterize the ascending vs. descending sort, although I have never tried. My first thought is to duplicate each line in the ORDER BY clause: one bind parameter for ASC and another for DESC. Have each CASE return a constant if the bound value is NULL, and then bind NULL for the direction you do not want. Yuck. I am not advocating any of this but am pointing out that bind parameters can be abused in surprising ways if you are backed into a corner.
solidsnack9000 12y ago
Even with PL/pgSQL, there is no way to build a SQL statement dynamically _and_ safely -- it's all just string concatenation there, too. However, using `CASE`, `WITH` and `LATERAL` you can have a root `SELECT` that returns one of a few different queryable code paths, and `WITH`/`LATERAL` allow you to reuse definitions.
- rpedela 12y ago
  Use format() for safely constructing dynamic queries. http://www.postgresql.org/docs/9.4/static/functions-string.html#FUNCTIONS-STRING-FORMAT http://www.postgresql.org/docs/9.4/static/functions-string.h...
  - solidsnack9000 12y ago
    You end up doing a lot of string concatenation even with that, though. It's not quite the same thing as having some kind of literal or combinator based setup. select(from('tab'), where(and(like('name','Mo%'),not(is('id',NULL)))
    - rpedela 12y ago
      You can get a similar syntax with format() by nesting format() calls such as: EXECUTE format('SELECT * FROM tab %s', format('WHERE %I IS NOT NULL', 'id'));
hobs 12y ago
Dynamic prepared(NO ADHOC, THIS IS CRAP) TSQL is a common occurrence for me in production, it is often a better choice that writing a bunch of conditional logic in SQL (to get sargeable queries) or a bunch of really unperformant case statements in where clauses. I have written TSQL that changes the entire query (which was on the order of 80 joins) dynamically based on about ~20 different parameters, only a few actually passed into the stored procedure, the rest based on settings and configuration. It took a few hours to grok how everything came together at first, but a few tricks that the optimizer will remove means you can add arbitrary conditionals in whatever order you please. The example you give is VERY trivial for dynamic SQL. postgres - http://www.postgresql.org/docs/9.1/static/ecpg-dynamic.html http://www.postgresql.org/docs/9.1/static/ecpg-dynamic.html mssql - https://msdn.microsoft.com/en-us/library/ms188001.aspx https://msdn.microsoft.com/en-us/library/ms188001.aspx
snambi 12y ago
Answer : Learn SQL. It is very easy to pass variables.

mbreese 12y ago

You might be over thinking this... something like this might work out just fine. (I wouldn't necessarily do things this way, but rather keep a list of clauses and join them with " AND " to avoid keeping track of the "WHERE"s and "AND"s, but you get the point...) sql = 'SELECT * FROM products' args = [] if title: sql += ' WHERE title LIKE %?%' args.append(title) if upc: if args: sql += ' AND' else: sql += ' WHERE' sql += ' upc LIKE %?%' args.append(upc) if price_min: if args: sql += ' AND' else: sql += ' WHERE' sql += ' price >= ?' args.append(price_min) if price_max: if args: sql += ' AND' else: sql += ' WHERE' sql += ' price <= ?' args.append(price_max) if order_by_name: sql += ' ORDER BY name' elif order_by_price_low: sql += ' ORDER BY price' elif order_by_price_high: sql += ' ORDER BY price DESC' conn.query(sql, args)

ad_hominem 12y ago
This is a query builder. If you're using your programming language to dynamically assemble the SQL statement fragments at runtime, then you're using a query builder regardless of if it is a library with a fancy DSL that assembles an in-memory SQL AST or some kind of ad-hoc string concatenation you rolled yourself like this. The question I'm asking is pointed towards the people who are implying that you can "just use SQL" as static statements that are not dynamically assembled. Like a static function or prepared statement that takes some parameters, and at runtime you only pass in those parameters - not rejigger the actual SQL statement fragments.
- mbreese 12y ago
  Yes, logically, it's a query builder. But it's one that doesn't force any extra dependencies in my project and is very easy to troubleshoot. Seriously, how is this different from "just use SQL". SQL was never really intended to be used by itself (except for manually typing queries into a console). Yes, what I wrote is code that writes code, but it's far different from a larger ORM or querying framework.
  - ad_hominem 12y ago
    Disregarding my edit and how your query builder is irrelevant to my question anyway, if I change the requirement to allow the user to sort by multiple columns instead of one, that case statement is going to either combinatorially explode or you'll also have to add some ad-hoc SQL escaping to guard against SQL injection since you can't use bound parameters in ORDER BY clauses. It's easy to see how libraries like Sequel get invented to help people do this, and I don't really see what the problem is when the DSL stays close to SQL semantics.
    - mbreese 12y ago
      Dealing with multiple ORDER BY clauses isn't any more difficult, but you'd have to figure out how to get the user input into some kind of list. That will require validation too... Just using an ORM doesn't let you ignore input validation. You wouldn't trust a user to use their own column names in a WHERE clause anymore than you'd trust them in an ORDER BY clause. Trying to differentiate between a DSL that interfaces with SQL and SQL is nonsensical. SQL is the original DSL.

hammerandtongs 12y ago

You've formulated the composibility reasonably in that sentence but it's important to remember that is not the only problem the world faces in data manipulation. I think the irony is that much of the problems of sql are related to a lack of tooling. The lack of tooling is result of large numbers of devs being distracted by ORM which moves that particular problem into the language ide/editor. Essentially we lack great *.sql editing, ide, macro, refactoring because of the lack of focus on sql itself. edit: as an aside, string concatenation in the language of choice is its own road to hell.

rdtsc 12y ago
> Essentially we lack great *.sql editing, ide, macro, refactoring because of the lack of focus on sql itself. Except that my other tools are just an editor like emacs and makefiles. So now to be productive I have to have and ide for sql, learn sql, learn macros. Interesting that SQL came first. If it was so easy and obvious to use it we would not have seen any ORMs by now. But every other project that uses SQL databases ends up with some kind of ORM. One of the reason is probably because there is an impedance mismatch. Code is already written dealing with objects, functions, pointers, data. But when it talks to the database it has to talk via a command line string interface so to speak. There is another type of impedance mismatch and that is learning a new language. One can argue SQL is there to keep & manipulate the most precious things you have -- data, so one should spend time learning it well. But the other point is, SQL is there to keep & manipulate the most precious things you -- data, so don't mess with it if you don't know SQL very well. Let someone else learn it (the author of the ORM). I am not arguing for one point or the other, just kind saying how it is interesting how we have been going in a circle the last 15 years or so.
- hammerandtongs 12y ago
  I also use emacs(evil-mode actually). An ide is not much different then a fully configured vim or emacs. sql-mode for some databases(mysql and oracle iirc) reads the DDL of the tables you are typing about, why is this not better and more general? Sql is so well established and general as a solution that not learning it is a poor choice.

moe 12y ago

How exactly are you going to "just write SQL" if the actual query statement needs to change based on the user input? How about something like this: s = Select.new s.add "WHERE title LIKE #{title}" if title s.add "WHERE price <= #{price_range}" if price_range s.add "LIMIT #{limit}" if limit s.add "OFFSET #{offset}" if offset s.add "id, blah, boo FROM products" s.execute Note how I deliberately shuffled the order and didn't bother with escaping. Also note how anyone who knows SQL could immediately work with this, learning curve: 5 seconds. Why is there no ORM that works like this?

ivanr 12y ago
There is. MyBatis, which is a minimal ORM that aims to keep you as close to SQL as possible, has support for dynamic SQL: https://mybatis.github.io/mybatis-3/dynamic-sql.html https://mybatis.github.io/mybatis-3/dynamic-sql.html Edit [responding to moe, below]: that's a matter of taste. I prefer to have my SQL _outside_ my code. If I have to write a little XML to make it happen, so be it. Additionally, unlike your example, a strict separation of SQL and data ensures SQL injection is not possible. That's also a worthy goal.
- moe 12y ago
  MyBatis Does MyBatis have an API similar to what I outlined above? Because the page that you link to shows a horror that I can't even begin to describe: <select id="findActiveBlogWithTitleLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> </select> SQL mixed with proprietary XML soup? Talk about combining the worst from all worlds...
  - bigpeopleareold 12y ago
    Get rid of the XML and you have a good start, I'd say.
zo1 12y ago
Python SQLAlchemy works a bit like this, if you use it that way: query = session.query(Products) if limit: query = query.limit(limit_val) if offset: query = query.offset(offset_val) Sort is a bit tricky, but as I'm a noob at SQL Alchemy, I had some not-so-pretty boilerplate code implemented for decorating queries with custom sorts. But essentially it boiled down to: if sort_type == SORT_REVERSE_CONST: sort_obj = sort_obj.desc() else: sort_obj = sort_obj.asc() query = query.order_by(sort_obj) And if you want to search through dynamic tables, change the initial query instantiation to something along these lines: query = session.query(retrieve_db_object(table_name)) And to execute: return query.all()

falcolas 12y ago

query = sprintf("select count(*) from data where %s = ?", column) results = sql.execute(query, filter_value); So long as you leave the "value" portion of the query as a '?' (or %s, or whatever your connector requires) and don't use direct user input for the column names, you're still safe from SQL injection.

rpedela 12y ago

That can be solved by writing a SQL function and the user input values are arguments to the function.

joshuahutt 12y ago

I would write a SQL function that takes all of those as optional parameters and includes a lot of these: WHERE (_title IS NULL OR title LIKE _title) AND (_minPrice IS NULL OR price > _minPrice) AND (_maxPrice IS NULL OR price < _maxPrice)

ad_hominem 12y ago
This actually answers the question, although I would imagine the ORDER BY handling will look pretty messy (CASE statement perhaps?). And I don't know what it would look like if the requirements changed to allow ordering by multiple columns with different possible sort directions... that might get back into dynamic SQL using a RETURN QUERY EXECUTE type of thing, which is basically using a query builder in your query language.
- babuskov 12y ago
  Most SQL engines support ordering by column number. select c1, c2, c3, ... order by 2; would order by "c2"
schrodinger 12y ago
That ends up being very inefficient as the optimizer will come up with one mediocre plan that works for all those criteria.

bigpeopleareold 12y ago

I'll deal with the problem when I have to. However, the point is that I'd rather use SQL, it's simple as that.

wallyhs 12y ago

> Or are there some advanced Postgres features that I'm not aware of that would allow all these combinations in some kind of prepared statement? I can't speak specifically to Postgres, but, in my experience, there is nearly always a way to do what you want with bound parameters. No advanced features are required. It often results in poor performance and redundant code that is hard on the eyes. You will get what you deserve, but sometimes you don't have a choice. Here is an example of the horror: WHERE (:1 IS NULL OR :1 = item_id) AND item_num BETWEEN NVL(:2,0) AND NVL(:2,9999999999) AND item_date BETWEEN NVL(:3,'01-jan-1900') AND NVL(:3,'31-dec-4712') AND item_name LIKE NVL(:4,'%') ORDER BY CASE :5 WHEN 'NUM' THEN item_num WHEN 'DATE' THEN item_date ELSE item_name END, CASE :6 WHEN 'NUM' THEN item_num WHEN 'DATE' THEN item_date ELSE item_name END Edit: I suppose you could also parameterize the ascending vs. descending sort, although I have never tried. My first thought is to duplicate each line in the ORDER BY clause: one bind parameter for ASC and another for DESC. Have each CASE return a constant if the bound value is NULL, and then bind NULL for the direction you do not want. Yuck. I am not advocating any of this but am pointing out that bind parameters can be abused in surprising ways if you are backed into a corner.

solidsnack9000 12y ago

Even with PL/pgSQL, there is no way to build a SQL statement dynamically _and_ safely -- it's all just string concatenation there, too. However, using `CASE`, `WITH` and `LATERAL` you can have a root `SELECT` that returns one of a few different queryable code paths, and `WITH`/`LATERAL` allow you to reuse definitions.

rpedela 12y ago
Use format() for safely constructing dynamic queries. http://www.postgresql.org/docs/9.4/static/functions-string.html#FUNCTIONS-STRING-FORMAT http://www.postgresql.org/docs/9.4/static/functions-string.h...
- solidsnack9000 12y ago
  You end up doing a lot of string concatenation even with that, though. It's not quite the same thing as having some kind of literal or combinator based setup. select(from('tab'), where(and(like('name','Mo%'),not(is('id',NULL)))
  - rpedela 12y ago
    You can get a similar syntax with format() by nesting format() calls such as: EXECUTE format('SELECT * FROM tab %s', format('WHERE %I IS NOT NULL', 'id'));

hobs 12y ago

Dynamic prepared(NO ADHOC, THIS IS CRAP) TSQL is a common occurrence for me in production, it is often a better choice that writing a bunch of conditional logic in SQL (to get sargeable queries) or a bunch of really unperformant case statements in where clauses. I have written TSQL that changes the entire query (which was on the order of 80 joins) dynamically based on about ~20 different parameters, only a few actually passed into the stored procedure, the rest based on settings and configuration. It took a few hours to grok how everything came together at first, but a few tricks that the optimizer will remove means you can add arbitrary conditionals in whatever order you please. The example you give is VERY trivial for dynamic SQL. postgres - http://www.postgresql.org/docs/9.1/static/ecpg-dynamic.html http://www.postgresql.org/docs/9.1/static/ecpg-dynamic.html mssql - https://msdn.microsoft.com/en-us/library/ms188001.aspx https://msdn.microsoft.com/en-us/library/ms188001.aspx

snambi 12y ago

Answer : Learn SQL. It is very easy to pass variables.