jeudi 22 octobre 2015

How do I sanitize these params for a user-supplied query?

I am seeing some Airbrake errors that tell me my users are having issues searching for things that PSQL thinks are REGEX when in fact it should treat it as a pure string.

The error is:

Parameters: {"terms"=>"P[", "id"=>"29"}
  Inventory Load (0.5ms)  SELECT name, number, id FROM "inventories" WHERE (name ~* 'P[' OR number ~* 'P[')
PG::InvalidRegularExpression: ERROR:  invalid regular expression: brackets [] not balanced
: SELECT name, number, id FROM "inventories"  WHERE (name ~* 'P[' OR number ~* 'P[')
Completed 500 Internal Server Error in 117.8ms

ActiveRecord::StatementInvalid (PG::InvalidRegularExpression: ERROR:  invalid regular expression: brackets [] not balanced
: SELECT name, number, id FROM "inventories"  WHERE (name ~* 'P[' OR number ~* 'P[')):

My code uses a the fuzzy search of PSQL to search the entire cell for a match anywhere in the string:

@inventory =
      Inventory.select("name, number, id")
        .where("name ~* :key OR number ~* :key", :key => key)

key is just passed into this method by the user from a search field in the UI. Someone tried to store an Inventory item as something like 'P[akjhdfkgjhdg]' and now they want to search for it. They should be able to.

Why does PSQL interpret it as REGEX?

Aucun commentaire:

Enregistrer un commentaire