Professional Web Applications Themes

Validating user-input to be inserted in regular expressions - PostgreSQL / PGSQL

Hi all, I would like to take user input, which should be interpreted literally, and put it inside a regular expression, something like select * from files where path ~ (USER_INPUT || '.*') How should I escape the user input? I mean: I know that I must insert double backslashes before special characters (in the sense of regular expressions) but how to know what these special characters are? Should I find every possible character in the doentation for regular expressions? Is there a safer way? Thanks Vincenzo Ciancia -- Please note that I do not read the e-mail address used ...

  1. #1

    Default Validating user-input to be inserted in regular expressions

    Hi all,

    I would like to take user input, which should be interpreted literally, and
    put it inside a regular expression, something like

    select * from files where path ~ (USER_INPUT || '.*')

    How should I escape the user input? I mean: I know that I must insert double
    backslashes before special characters (in the sense of regular expressions)
    but how to know what these special characters are? Should I find every
    possible character in the doentation for regular expressions? Is there a
    safer way?

    Thanks

    Vincenzo Ciancia

    --
    Please note that I do not read the e-mail address used in the from field but
    I read vincenzo_ml at yahoo dot it
    Attenzione: non leggo l'indirizzo di posta usato nel campo from, ma leggo
    vincenzo_ml at yahoo dot it


    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Vincenzo Ciancia Guest

  2. #2

    Default Re: Validating user-input to be inserted in regular expressions

    Vincenzo Ciancia wrote:
    > Should I find every
    > possible character in the doentation for regular expressions?
    Is the answer trivial? I checked the manual and the FAQ, and googled for the
    answer, but I didn't find it. Is there a more appropriate place where I can
    ask my question?

    Thanks

    Vincenzo

    --
    Please note that I do not read the e-mail address used in the from field but
    I read vincenzo_ml at yahoo dot it
    Attenzione: non leggo l'indirizzo di posta usato nel campo from, ma leggo
    vincenzo_ml at yahoo dot it


    ---------------------------(end of broadcast)---------------------------
    TIP 8: explain yze is your friend

    Vincenzo Ciancia Guest

  3. #3

    Default Re: Validating user-input to be inserted in regular expressions


    On Jan 25, 2005, at 8:48 AM, Vincenzo Ciancia wrote:
    > Vincenzo Ciancia wrote:
    >
    >> Should I find every
    >> possible character in the doentation for regular expressions?
    >
    > Is the answer trivial? I checked the manual and the FAQ, and googled
    > for the
    > answer, but I didn't find it. Is there a more appropriate place where
    > I can
    > ask my question?

    Yes, this is a fine place to ask your question. Sometimes it does take
    a day or two to get an answer. Will quote_literal do what you want?

    [url]http://www.postgresql.org/docs/8.0/interactive/functions-string.html[/url]

    Search on that page for quote_literal.

    Sean


    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Sean Davis Guest

  4. #4

    Default Re: Validating user-input to be inserted in regular expressions

    Sean Davis wrote:
    > Yes, this is a fine place to ask your question.Sometimesitdoestake
    > a day or two to get an answer.Willquote_literaldowhatyouwan t?
    >
    > [url]http://www.postgresql.org/docs/8.0/interactive/functions-string.html[/url]
    >
    > Search on that page for quote_literal.
    >
    Thank you for your answer. Unfortunately quote_literal is not what I am
    looking for, in fact it quotes special characters in the sense of strings,
    not in the sense of regular expressions. Here's some example to explain my
    problem a little better:

    I would like to select strings that begin with 'a.', so I do NOT want the
    following (suppose 'a.' is generic user input)

    relfs=# select true where 'aa' ~ ('a.'||'.*');
    bool
    ------
    t

    I could as well use 'a\\.', but what characters should I escape? Surely
    $^+.*[] and possibly others. The function quote_literal does:

    relfs=# select true where 'aa' ~ (quote_literal('a.')||'.*');
    bool
    ------
    (0 righe)

    but it's mere illusion :) In fact we have:

    relfs=# select true where '\'aa\'' ~ (quote_literal('a.')||'.*');
    bool
    ------
    t

    Thanks for any suggestions

    Vincenzo

    --
    Please note that I do not read the e-mail address used in the from field but
    I read vincenzo_ml at yahoo dot it
    Attenzione: non leggo l'indirizzo di posta usato nel campo from, ma leggo
    vincenzo_ml at yahoo dot it


    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Vincenzo Ciancia Guest

  5. #5

    Default Re: Validating user-input to be inserted in regular expressions

    On Tue, Jan 25, 2005 at 04:28:06PM +0100, Vincenzo Ciancia wrote:
    > Thank you for your answer. Unfortunately quote_literal is not what I am
    > looking for, in fact it quotes special characters in the sense of strings,
    > not in the sense of regular expressions.
    It sounds like you're looking for the equivalent of Perl's quotemeta:

    % perl -le 'print quotemeta "abc.*"'
    abc\.\*

    I'm not aware of any such function in PostgreSQL, but you could use
    a PL/Perl function that simply calls quotemeta:

    CREATE FUNCTION quotemeta(text) RETURNS text AS '
    return quotemeta $_[0];
    ' LANGUAGE plperl IMMUTABLE STRICT;

    SELECT quotemeta('abc.*');
    quotemeta
    -----------
    abc\.\*
    (1 row)

    There might be differences between PostgreSQL's and Perl's regular
    expression engines, but perhaps not enough to matter in this case.

    I expect it would be easy to add such a function to PostgreSQL, so
    consider suggesting it to the developers or even writing it yourself
    and submitting a patch.

    --
    Michael Fuhr
    [url]http://www.fuhr.org/~mfuhr/[/url]

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Michael Fuhr Guest

Similar Threads

  1. RE : RE : RE : Regular expressions
    By Steve Hemond in forum PERL Beginners
    Replies: 3
    Last Post: December 17th, 07:38 PM
  2. [PHP] Q on Regular Expressions
    By Curt Zirzow in forum PHP Development
    Replies: 1
    Last Post: September 17th, 09:49 PM
  3. Validating Subdomain E-mail Addresses Using Regular Expressions
    By Ivo Fokkema in forum PHP Development
    Replies: 0
    Last Post: September 10th, 08:01 AM
  4. Replies: 2
    Last Post: September 8th, 03:08 PM
  5. validating date input by user
    By NicNel in forum Microsoft Access
    Replies: 2
    Last Post: July 28th, 09:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139