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

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.)

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:

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.

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!

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

Great!

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.