SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    RegExp to check structure of an sql 'where clause'

    I've decided that the easiest way for registered users to perform searches in the database system I've made for them is by letting them write an SQL like 'WHERE clause' instead of offering them pull-down menus and so on.

    So I really need a good regexp for analysing the clause string. It must have a structure something like this:

    FieldName_$1 Operator_$1 Value_$1 Boolean_$2 FieldName_$2 Operator_$2 Value_$2 ... Boolean_$n FieldName_$n Operator_$n Value_$n

    'Real life' example:
    "Production Year" > 1960 AND "Color" = "5" ... OR "Color" < "3"

    As you may notice, I operate with field name aliases instead of real database field names, and these aliases I want the user to write inside quotes because they may contain spaces (and even periods), like this: "Inv. No". I can use PHP to generate a regexp pattern that contains the field name aliases.

    P.S. I guess it should be allowed to group with parentheses to.

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    16,492
    Mentioned
    164 Post(s)
    Tagged
    1 Thread(s)

    user 'where' clauses

    Hi smergler, welcome to the forums,
    Quote Originally Posted by smergler
    I've decided that the easiest way for registered users to perform searches in the database system I've made for them is by letting them write an SQL like 'WHERE clause' instead of offering them pull-down menus and so on.
    IMHO it would be much easier for them to use predefined drop-down selects rather than to learn correct SQL syntax.
    It may take you a while to write the needed code, but in the long run I think it would be easier and less worrisome than relying on a regex to validate user inputs.
    Check out the source code for my Error Reporting plugin and you may see something helpful.

  3. #3
    SitePoint Addict Wildhoney's Avatar
    Join Date
    Apr 2006
    Location
    Nottingham
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As Mittineague correctly said, from a usability perspective issuing the user with a series of select boxes is the way forward. The last thing you are going to want to be doing is alienating your visitors through bad design. That is an example of a bad design that is bordering on the edge of esoteric knowledge. In other words, people need to be somewhat acquainted with the meaning of <, >, <>, etcetera...
    TalkPHP.com - The Friendly PHP Community

    Watch Reaper Online - Watch Chuck Online

  4. #4
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course I agree, and please beleive me when I say I have made such a solution considerable thought. But the users that are targeted here are academics and many of them are already somewhat familiar with SQL. They will, as I said, only be allowed to perform these very crude where clauses, and I want them to be able to do it this way because it will be much less time consuming for them.

    (All actions are logged with user's full name and a system for rollback is there).

    Convinced? :-)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    on an intranet, should one guard against sql injection?

    even if all actions are logged with the user's full name, this in itself does not prevent a user's name/password from becoming compromised
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Aug 2007
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, but I'm actually, through regexp, just looking for a confirmation of correct syntax. Every element of the input will be checked as one would check other input sent from a form.


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
  •