Order by query using dart and postresql

Question

When running this query using dart and the postresql driver

  getPost(int limit, int offset, String order_by){
    connect(uri).then((conn){
      conn.query('''select * from posts
                    order by @order_by
                    limit @limit offset @offset''', {'limit': limit, 'offset': offset, 'order_by': order_by})
                    .toList()
                    .then((rows){
        print(rows);
      })
      .whenComplete(() => conn.close());
    })
    .catchError((err) => print('Error in getPost: $err'));
  }

I get the Error: 42601 non-integer constant in ORDER BY.

The code above is a method of a helper class. I run it with the following code.

dbUtil.getPost(10, 10, "posted_at");

I read here that postgresql expects a string literal. That's why I also tried this code dbUtil.getPost(10, 10, r"posted_at"); with no success. If I replace @order_by with posted_at the query returns the values correctly.

Does anybody know how to solve this problem?

Working code inspired by the answer below.

  getPost(int limit, int offset, String order_by){
    connect(uri).then((conn){
      var sb = new StringBuffer();
      sb.write("select * from posts ");
      sb.write(order_by);
      sb.write(" limit @limit offset @offset"); 
      String query = sb.toString();
      conn.query(query, {'limit': limit, 'offset': offset})
                    .toList()
                    .then((rows){
        print(rows);
      })
      .whenComplete(() => conn.close());
    })
    .catchError((err) => print('Error in getPost: $err'));
  }

I added a more dartish example to my answer. You also don't connect the futures (missing returns). I didn't change this in my example because I don't know the intention. — Günter Zöchbauer, May 12 '15 at 13:31
I don't think you need the use a StringBuffer to build the query. I believe the bug in the first example is that you are missing the order_by parameter in the map passed to query(). This is probably causing a null to be substituted into your query leading to the odd error message. Try turning on query logging on your db, then you will be able to see the exact text of your queries. — Greg Lowe, May 13 '15 at 09:07
Unfortunately you do need to build the string somehow. My code was not missing the `'order_by': order_by` part. I just wrongfully inserted it in the question. — Lukasz, May 13 '15 at 14:17
@GregLowe I fixed the code so it looks like it actually used to. By the way are you interested in a [bounty](http://stackoverflow.com/questions/30107414/sessions-in-dart?noredirect=1#comment48491159_30107414)? — Lukasz, May 13 '15 at 14:51

Günter Zöchbauer · Accepted Answer · 2015-05-13T09:15:10.663

I don't think using bound variables for column names (same for table names, index names, ...) is supported by the SQL standard. These names need to be hardcoded in the SQL statement using for example String concatenation or interpolation.

But ensure you don't use any values from user input here and/or do proper validation and sanitiy checks to not introduce any SQL injection backdoors.

The PostgreSql package doesn't process the SQL you pass to execute, it only forwards it to the database in the required format and parses the results (not true currently, but it should work this way, see comment below).

An example:

bool isValidSqlColumnName(String columnName) {
  return new RegExp(r'^[a-zA-Z_][a-zA-Z0-9_]*$').hasMatch(columnName);
}

getPost(int limit, int offset, String order_by){
    if(!isValidSqlColumnName(order_by)) {
      throw 'Only valid column names are accepted.';
    }
    connect(uri).then((conn){
      conn.query('''select * from posts
                    order by $order_by
                    limit @limit offset @offset''', {'limit': limit, 'offset': offset, 'order_by': order_by})
                    .toList()
                    .then((rows){
        print(rows);
      })
      .whenComplete(() => conn.close());
    })
    .catchError((err) => print('Error in getPost: $err'));
  }

Currently the postgresql package just uses the simple query protocol which means that all parameter substitution is done by the client in the driver, and a raw sql string is sent to the server. So you can actually substitute order_by using @order_by (I think the bug is that Lukasz forgot to add this to the map passed to query()). This will change one day when I get around to implementing more of the postgresql protocol. — Greg Lowe, May 13 '15 at 09:11
I guess it's better to not mention it because it would be a breaking change then ;-) — Günter Zöchbauer, May 13 '15 at 09:13
Good point. But, the syntax will be different too, postgresql uses $ dollar signs, and my driver uses @ signs. Though I guess I could write a translation mode for backwards compatibility. — Greg Lowe, May 13 '15 at 09:15

Order by query using dart and postresql

1 Answers1