Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Red face Unanswered: Specifying my search

    Hi, this is my first post and im pretty new to perl and i need some help in refining my search. This is what i am trying to do:

    Create a webform which will allow a user to query a job listing from a database. The web form should allow the user to specify or select a department and on submission return a list of jobs in that department.

    I can query the database fine using the SQL language but im not sure how to specify my search to one particular department. I though of using radio buttons for each department so that when the use selects a particular department e.g. "Social Services" it concentrates their job search to that department.

    Here is my code:

    ---------------------------------------------------------------------
    #!c:/perl/bin/perl

    use strict;
    use DBI;
    use DBD::ODBC;
    use CGI ':standard';
    use CGI::Carp 'fatalsToBrowser';


    my $mydb = "JOBS";


    # Connect to database

    my $dbh = DBI->connect( "dbi:ODBC:$mydb" );

    print header, start_html( { title => "Job's Database"} );


    if ( !param() ) {
    # Use CGI.pm functions to set up HTML form
    print h1( "Jobs Database Search" );
    print hr, br;

    print start_form( { action=>'http://localhost/cgi-bin/jobDB.cgi'} );

    print 'Please select the department you are interested in: ', br;
    print '<INPUT TYPE="radio" Name = "department" value="Social Services">', 'Social Services', br;
    print '<INPUT TYPE="radio" Name = "department" value="Building Services">', 'Building Services', br;
    print '<INPUT TYPE="radio" Name = "department" value="Personnel">', 'Personnel', br;
    print '<INPUT TYPE="radio" Name = "department" value="Engineering">', 'Engineering', br;
    print '<INPUT TYPE="radio" Name = "department" value="Hospitality">', 'Hospitality', br;
    print '<INPUT TYPE="radio" Name = "department" value="Pharmacy">', 'Pharmacy', br;
    print '<INPUT TYPE="radio" Name = "department" value="Information Systems">', 'Information Systems', br, br;


    print "Now, please enter your job query for that department: ", textfield( -name=>'sql_query',
    -default=>'',
    -size=>80,
    -maxlength=>80),
    br;
    print br, submit( -value => "Submit Query" );
    print reset( -value => "Reset Query" );
    print hr;
    print end_form;
    }
    else {

    # Receive SQL query from form
    my $sql = param('sql_query');

    # Prepare SQL statement for execution
    my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh -> errstr();

    # Execute prepared statement
    $sth->execute() or die "Cannot execute: " . $sth->errstr();

    print h2("Job's Found:");

    # Declare array @row to receive records from query
    my @row;

    # Use the DBI function fetchrow_array() to return a row of field values (i.e. a record) to @row
    # Use the Perl join() function to combine the fields, separated by commas, for output

    print '<TABLE BORDER=1><TH>Job Query Ran</TH>';
    print "<TR><TD> $sql </TD></TR></TABLE>", br, br;

    print '<TABLE BORDER=1><TH>Results</TH>';
    while (@row = $sth -> fetchrow_array() ) {
    #print join(",", @row), br;
    print "<TR><TD>@row</TD></TR>";
    }

    # Finish the SQL statement execution
    $sth -> finish();

    # Disconnect from database
    $dbh->disconnect();
    }

    print end_html;

    ---------------------------------------------------------------------

    Can anyone help?

    Oh and the table's name is "jobs" and the field name for department is "job_dept" Any help would be appreciated, am i on the right lines??

    Cheers

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Code:
    # Receive SQL query from form
        my $sql = param('sql_query');
    This is about the worst thing you could possibly do.

    When a form submission comes in, the other user can fill out any field any way they please. So in this case, the user can send any SQL statement to your database, and it is possible, with SQL, to completely take over your system.

    And if it's for your personal use, or you're just experimenting, or whatever: lots of people could read this post, and some of them will inevitably copy and paste your code without thinking.

    If you need to experiment with different queries, incidentally, it's easier to use DBI Shell than to muck about with a CGI mysteriously returning 500 Internal Server Error because you forgot a semicolon.

    So, the right way:

    I'll have to guess at the SQL, but this is generally how you'd do it:

    (See this if the q{ } is confusing.)

    Code:
    my $sql = q{
    SELECT FieldA, FieldB, FieldC
    FROM Jobs
    WHERE Department = ?
    }; 
    
    my $department = param('department');
    
    # Prepare SQL statement for execution
        my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh -> errstr();
        
    # Execute prepared statement
        $sth->execute($department) or die "Cannot execute: " . $sth->errstr();
    The question mark is a placeholder. You'll notice that execute now takes a parameter. This is called a "bind value."

    Perl allows you to "interpolate" variables into your SQL. This is almost always a bad idea because it allows for SQL injection. If at all possible, use bind variables. One case where it is difficult is if you using an IN expression in a where clause. A reasonable compromise is this:

    Code:
    my(@inArray) = ('this', 'that', 'whatever');
    my $inList = join(', ', map($dbh->quote($_), @inArray));
    # Or if that's confusing:
    my @inArrayQuoted = ();
    foreach my $item (@inArray) {
      push @inArrayQuoted, $dbh->quote($item);
    }
    my $inList = join(', ', @inArrayQuoted);
    
    my $sql = qq{
    SELECT Foo, Bar FROM Qux WHERE Foo IN ($inList)
    };
    But if you forget to quote in just one place, you open the possibility of SQL injection. If you never interpolate, it's much, much harder for some joker to run his code on your DBMS.

    There are more advanced binding techniques that let you carefully specify the parameter type. But DBI usually gets it right, so there's not much call for it unless you're trying to tweak performance or do something complicated with BLOBs.

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    Cheers for the reply and the advice on the DB.
    This database isn't very important and it won't be run on the internet so an outside user can't tamper with it.

    To be honest I have only been learning perl for the past couple of months and am by no means good at it. Therefore, I don't really fully understand your answer :/

    Cheers anyway!

  4. #4
    Join Date
    Nov 2009
    Posts
    3

    It's done!

    Hello again - Just wanted to post up that I managed to resolve the issue I had using some of the code you gave me mate.

    The code also puts the results into a table:

    else {

    my $sql = q{
    SELECT *
    FROM jobs
    };

    my $dept = param('department');
    my $sth = $dbh-> prepare($sql) or die "Cannot prepare: " . $dbh -> errstr();
    $sth -> execute();

    print h2("Job's Found:");

    print '<TABLE BORDER=1><TH>Job Reference</TH><TH>Job Title</TH><TH>Department</TH><TH>Closing Date</TH>';

    while ( my @row = $sth -> fetchrow_array() ){
    if ( $row[3] =~ m/$dept/ )
    {
    print "<TR><TD>$row[0]</TD><TD>$row[1]</TD><TD>$row[3]</TD><TD>$row[4]</TR>", br, br;
    }
    }

    print '</TABLE>';

    $sth -> finish();
    $dbh->disconnect();
    }

    print end_html;


    Now if i can only get rid of the big white space it leaves between the title and the results table I'll be happy.

    Cheers Scooby

  5. #5
    Join Date
    Sep 2009
    Posts
    44
    Great!

    To be honest I have only been learning perl for the past couple of months and am by no means good at it. Therefore, I don't really fully understand your answer :/
    Yeah, I never know how much people understand so I just throw out a bunch of stuff and hope some of it sticks. The nice thing about perl is that you can do a lot only understanding a little and work your way up from there.

Posting Permissions

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