# Variable numbers of terms in a query

**URL:** <https://www.ruby-forum.com/t/variable-numbers-of-terms-in-a-query/104417>\
**Category:** Rails\
**Created:** [May 17, 2007, 6:54pm UTC](https://www.ruby-forum.com/t/variable-numbers-of-terms-in-a-query/104417 "2007-05-17T18:54:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![milo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85e7bf/32.png) [@milo](https://www.ruby-forum.com/u/milo)\
**Post date:** [May 17, 2007, 6:54pm UTC](https://www.ruby-forum.com/t/variable-numbers-of-terms-in-a-query/104417/1 "2007-05-17T18:54:34Z")

</div>

I have to produce a front end to a database where users can select from  
a variety of parameters they want to search on, e.g.

a \> 1  
b = 2  
c \>= 3

The numerical values could be floats as well as integers, and so a  
drop-down list isn’t really convenient.  
I can assemble a string along the lines of “a \> 1 and b = 2 and c \>= 3”  
and insert it into another command, but if I do it like this:

@things = Thing.find(:all, :conditions =\> ["? and foreign\_key\_id is NOT  
NULL", @findstring])

…it won’t work as the symbols will be quoted, and putting the string  
directly into an SQL query would be bad as users could enter anything in  
those text boxes.  
Can anyone suggest a way around this?

---

<div class="post-metadata">

**Author:** ![milo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85e7bf/32.png) [@milo](https://www.ruby-forum.com/u/milo)\
**Post date:** [May 17, 2007, 7:35pm UTC](https://www.ruby-forum.com/t/variable-numbers-of-terms-in-a-query/104417/2 "2007-05-17T19:35:28Z")

</div>

On Thu, May 17, 2007 at 06:54:34PM +0200, Milo T. wrote:

> and insert it into another command, but if I do it like this:
> 
> @things = Thing.find(:all, :conditions =\> ["? and foreign\_key\_id is NOT  
> NULL", @findstring])
> 
> …it won’t work as the symbols will be quoted, and putting the string  
> directly into an SQL query would be bad as users could enter anything in  
> those text boxes.  
> Can anyone suggest a way around this?

Ruby is your friend. I’m going to assume that you are receiving this in  
params in an action something like this:

params[“db\_terms”] = {  
“a” =\> { “oper” =\> “\>”, “value” =\> “1” },  
“b” =\> { “oper” =\> “=”, “value” =\> “2” },  
“c” =\> { “oper” =\> “\>=”, “value” =\> “3” }  
}

I’m also assuming you have a canonical list of acceptable field names  
(as  
strings) in a constant named KnownFields and a similar list of  
acceptable  
operators (as strings) in KnownOperators. Since we need a dependable  
(but  
not necessarily sorted) order, we’ll turn it into an array instead of a  
hash first, then produce a condition string:

db\_terms = params[:db\_terms].select { |field,condition|  
KnownFields.include?(field) &&  
Hash === condition &&  
KnownOperators.include?(condition[“oper”]) &&  
!condition[“value”].blank?  
}

If a value or operator is missing or left empty or an unknown field or  
operator is maliciously inserted in the query params, this will get rid  
of  
that part of the query. Next is the easy part:

if db\_terms.empty?

# What do you do when no valid conditions are given?

else  
condition\_string = db\_terms.map { |field,condition|  
“#{field} #{condition[‘oper’] ?” }.join(’ AND ')  
condition\_values = db\_terms.map { |field,condition| condition[‘value’]  
}  
@things = Thing.find :all,  
:conditions =\> [ “#{condition\_string} AND foreign\_key\_id IS NOT  
NULL”,  
\*condition\_values ]  
end

–Greg

---

<div class="post-metadata">

**Author:** ![milo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85e7bf/32.png) [@milo](https://www.ruby-forum.com/u/milo)\
**Post date:** [May 17, 2007, 8:09pm UTC](https://www.ruby-forum.com/t/variable-numbers-of-terms-in-a-query/104417/3 "2007-05-17T20:09:59Z")

</div>

Gregory S. wrote:

> Ruby is your friend. I’m going to assume that you are receiving this in  
> params in an action something like this:
> 
> params[“db\_terms”] = {  
> “a” =\> { “oper” =\> “\>”, “value” =\> “1” },  
> “b” =\> { “oper” =\> “=”, “value” =\> “2” },  
> “c” =\> { “oper” =\> “\>=”, “value” =\> “3” }  
> }

Indeed so.

> I’m also assuming you have a canonical list of acceptable field names  
> (as  
> strings) in a constant named KnownFields and a similar list of  
> acceptable  
> operators (as strings) in KnownOperators.

I do have a list of acceptable field names, which is defined in the  
thing model and called with Thing.params. I did come up with this  
method, but I don’t think it’s very good.

Thing.params.each do |param|  
if params[:search\_param][param] # i.e. if a, b, c selected etc.  
findarray \<\< “p.#{param} #{params[:param\_sym][param]}  
‘#{params[:param\_value][param]}’”  
end  
end

The idea of params[:param\_sym][param] is so that only allowed operators  
from a set of radio button options can be selected, which are defined in  
the view. The bit in escapted single quotes is the text from the user. I  
then join findarray on " and " and insert it into the search string.

Your system looks rather less crude than mine, so I’ll give it a try.  
Many thanks.

---

<div class="post-metadata">

**Author:** ![milo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85e7bf/32.png) [@milo](https://www.ruby-forum.com/u/milo)\
**Post date:** [May 17, 2007, 8:52pm UTC](https://www.ruby-forum.com/t/variable-numbers-of-terms-in-a-query/104417/4 "2007-05-17T20:52:40Z")

</div>

On Thu, May 17, 2007 at 08:09:59PM +0200, Milo T. wrote:  
[…]

> The idea of params[:param\_sym][param] is so that only allowed operators  
> from a set of radio button options can be selected, which are defined in  
> the view. The bit in escapted single quotes is the text from the user. I  
> then join findarray on " and " and insert it into the search string.

You can’t trust anything coming to you over the net. It doesn’t matter  
if  
you only have radio buttons with acceptable values in the HTML you send.  
Any malicious user can send any data whatsoever in your params. You must  
always validate on the server side regardless of any validation or  
restrictions you have in HTML or JS.

> Your system looks rather less crude than mine, so I’ll give it a try.  
> Many thanks.

Good luck.

–Greg

---

<div class="post-metadata">

**Author:** ![milo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/85e7bf/32.png) [@milo](https://www.ruby-forum.com/u/milo)\
**Post date:** [May 18, 2007, 10:37am UTC](https://www.ruby-forum.com/t/variable-numbers-of-terms-in-a-query/104417/5 "2007-05-18T10:37:00Z")

</div>

Gregory S. wrote:

> You can’t trust anything coming to you over the net. It doesn’t matter  
> if  
> you only have radio buttons with acceptable values in the HTML you send.  
> Any malicious user can send any data whatsoever in your params. You must  
> always validate on the server side regardless of any validation or  
> restrictions you have in HTML or JS.

Thanks. Would something like this be sufficiently secure, or have I  
still missed something? It is supposed to look up the values for the  
search term and operator in an array or hash respectively, and  
single-quote any text values passed in by the user.  
This is, again, probably not very good but I would be interested to know  
in what way it is flawed.

# search terms collected in an array

findarray = []

# form passes in a number to represent the operator

operators = { “1” =\> “=”,  
“2” =\> “\<”,  
“3” =\> “\>”,  
“4” =\> “\<=”,  
“5” =\> “=\>”,  
“6” =\> “!=” }

# loop through each of the known search terms

# to see if it is defined. If it is, and the operator

# value is found in the hash, then add a search term

# to the array

# params[:search\_param] - the search term in the database

# params[:param\_sym] - an integer denoting which operator to use

# params[:param\_value] - the user’s text input

Thing.params.each do |param|  
if params[:search\_param][param] &&  
operators[params[:param\_sym][param]]  
findarray \<\< “p.#{param} #{symbols[params[:param\_sym][param]]}  
‘#{params[:param\_value][param]}’”  
end  
end

# if there’s anything in the array, form

# search string from it

@findstring == “”  
unless findarray.empty?  
@findstring = " and " + findarray.join(" and ")  
end

# actually run the search

@things = Thing.find\_by\_sql(“select t.\* from things t, params p where  
t.id = p.thing\_id #{@findstring} and p.thing\_id is NOT NULL”)