This is how I create my search_term
:
IF char_length(search_term) > 0 THEN
order_by := 'ts_rank_cd(textsearchable_index_col, to_tsquery(''' || search_term || ':*''))+GREATEST(0,(-1*EXTRACT(epoch FROM age(last_edited)/86400))+60)/60 DESC';
search_term := 'to_tsquery(''' || search_term || ':*'') @@ textsearchable_index_col';
ELSE
search_term := 'true';
END IF;
I am having some trouble with a PLPGSQL function:
RETURN QUERY EXECUTE '
SELECT
*
FROM
articles
WHERE
$1 AND
' || publication_date_query || ' AND
primary_category LIKE ''' || category_filter || ''' AND
' || tags_query || ' AND
' || districts_query || ' AND
' || capability_query || ' AND
' || push_notification_query || ' AND
' || distance_query || ' AND
' || revision_by || ' AND
' || publication_priority_query || ' AND
' || status_query || ' AND
is_template = ' || only_templates || ' AND
status <> ''DELETED''
ORDER BY ' || order_by || ' LIMIT 500'
USING search_term;
END; $$;
returns ERROR:
argument of AND must be type boolean, not type text at character 64
As opposed to:
RETURN QUERY EXECUTE '
SELECT
*
FROM
articles
WHERE
' || search_term || ' AND
' || publication_date_query || ' AND
primary_category LIKE ''' || category_filter || ''' AND
' || tags_query || ' AND
' || districts_query || ' AND
' || capability_query || ' AND
' || push_notification_query || ' AND
' || distance_query || ' AND
' || revision_by || ' AND
' || publication_priority_query || ' AND
' || status_query || ' AND
is_template = ' || only_templates || ' AND
status <> ''DELETED''
ORDER BY ' || order_by || ' LIMIT 500';
END; $$;
... which works. Am I missing something?
My goal is to sanitize my user input.
$1
search_term
? If it is something likecolumn_a = 'some_string'
then it won't work with prepared statement as those can't be used for dynamic SQL.search_term
is user input a.k.a. an arbitrary string value.search_term
cannot be just an arbitrary string value. It must be an expression evaluating to a boolean value when executed - or your 2nd code fragment would error out as well. Please show a SSCCE and some sample input. Don't dump your whole code fragment with lots of irrelevant noise but missing essential parts. For starters, both your attempts are no good to sanitize user input. Gaping SQL injection holes ...