1

I would like to use postgresql-simple for my database access layer in my application and i was wondering what the best method would be for dynamically building my sql queries. A simple example of what i would want to accomplish would be the following:

A user has a series of filters than can apply to the things they are searching:

  • Number of pets
  • Name of pet
  • When pet was created
  • etc

How should i handle composing the filters such that i dont make myself susceptible to attacks. My frst instinct would be that i would need to only worry about String/Text data since i can check that the number that comes in is actually a number and the date supplied is actually a date. Using the following format:

makeNumberOfPetsFragment :: String -> Either String String
makeNumberOfPetsFragment candidate = 
  case createStatement of
   Left _ -> Left "Error creating number of pets filter"
   Right x -> Right ("WHERE number_of_pets = " ++ (show x)) 
  where createStatement = readEither candidate :: Either String Int

I'm not sure if this is just a bad approach or if it would be better to just use existing libraries or if there is a better approach in pre made statements that i just am not thinking about.

Fyodor Soikin
  • 78,590
  • 9
  • 125
  • 172
emg184
  • 850
  • 8
  • 19
  • 1
    To protect from SQL injection, the usual approach is to use parameterized queries. – Fyodor Soikin Sep 17 '19 at 00:10
  • 1
    How would i use paramterized queries before hand if i am not sure what filters would be supplied? With each filter wouldn't i need to add another argument to the tuple of (?,?,?) that i am passing. – emg184 Sep 17 '19 at 00:17
  • 2
    You assemble the query and the parameter list separately in parallel, each filter adding the part of the query and the parameters that it needs. Once everything is assembled, you issue the query. – Fyodor Soikin Sep 17 '19 at 00:19
  • @FyodorSoikin how do you dynamically assemble a tuple in haskell? The postgresql-simple library takes tuples as parameters. – decapo Dec 15 '21 at 17:58
  • 1
    This was awhile ago but the way i ended up doing this was leveraging the ```ToField a => ToRow [a]``` instance you can easily make a list containing the substitution values by calling ```[Existing ToFields] ++ [toField val]``` that got the job done for me. @decapo – emg184 Dec 15 '21 at 18:19
  • I don't quite understand what you mean by "dynamically assemble a tuple" @decapo – Fyodor Soikin Dec 15 '21 at 18:50

0 Answers0