Implementing Faceted search

Anvil Q&A
9

This is all a good start! Although I would like to double and triple the warnings about SQL injection. Please, please be careful here, and use the proper substitution mechanisms in the Python database libraries. Not doing this is the #1 way to get your app hacked.

If you’re going to build up an SQL query as a string here’s how I would do it (based on David’s code):


  def do_query(name_op, name, age_op, age, married_op, married):
    sql = "SELECT name,age,married FROM mytable WHERE TRUE"
    sql_args = []

    # Check first column.
    if name_op is not None:
      operator = get_operator(name_op)
      sql += " AND name " + operator + " %s"
      sql_args.append(name)

    # check second column
    if age_op is not None:
      operator = get_operator(age_op)
      sql += " AND age " + operator + " %s"
      sql_args.append(age)

    cur = #...get a DBAPI cursor...

    # cur.execute() will substitute all those arguments in *safely*:
    cur.execute(sql, sql_args)

    return list(cur)

A few notes:

  • I’ve also recast this function as a server function, so you don’t get into the habit of trusting client code to construct SQL for you

  • It now expects you to pass age in as a number (so you’ll have to pass something like int(self.age_text_box.text) from the client), and to pass None for the operator parameters to signal “don’t filter on this column”.

  • Note that this code still trusts the get_operator() function absolutely, so this function must also be on the server, and be sure to only return a white-listed set of SQL operators.

@david.wylie, can I ask you a favour? Your code is clearly going to be used as a template by readers, however many warnings you put on it, so could I ask you to incorporate my changes (or something like them) to make it safe by default?

Roadmap note: We are going to be adding support for the necessary queries for faceted search (greater than/less than/between) to our built-in data table system soon! For Chris’s particular use case, he will probably still want Postgres, but for everyone else, we want to save them from the dangers of playing with fire manually building SQL queries.