CGIScripter
Features and Benefits
CGIScripter
Demo Available for download...
Bookmark
This Page
Flowchart
#!/usr/bin/perl
# Script: example_delete_one_record1
# Features: This Perl program accepts the
# primary key value passed in via an
# HTTP POST then deletes that one
# record from the database. For security
# reasons the primary key column name
# is hard coded into this script.
# Also, the referer URL must match
# the Display Record Script - or the
# record will not be deleted.
#
# Requirements:
# Perl CGI module
# Perl DBI module
# Perl DBD::mysql module
# MySQL client software must be installed on the
# webserver running this program.
#
# Database Connection:
# MySQL Connection: database=mysql1:host=mysqlhost1:port=3306
#
# External Files:
# HTML Query Form: example_delete1.html
# Display Record Script:example_delete_record_display1
# Delete Record Script: example_delete_one_record1
#
#
#
# Usage: Delete Record POSTS To: http://www.dotcomsolutionsinc.net/cgi-bin/example_delete_record_display1
#
# Copyright 2003 by .com Solutions Inc.
#
# ---------------------- Revision History ---------------
# Date By Changes
# 6-27-2003 dsimpson Initial Release
#
# This output file was created by CGIScripter version 1.48 on Sat Jul
12 10:14:55 2003. By .com Solutions Inc. www.dotcomsolutionsinc.net
#
use strict;
use DBI qw(:sql_types);
use CGI qw(:standard escape);
$CGI::POST_MAX=10000; # max post size - to avoid
denial of service attacks
$CGI::DISABLE_UPLOADS = 1; # 1 = no uploads of files allowed via CGI.pm
- for security
my $db_connect_string = 'database=mysql1:host=mysqlhost1:port=3306';
my $database_tablename = 'asset_management2';
my $schema_name = 'mysqluser1';
my $schema_password = 'mysqluser1pwd';
my $debug = 0; # DBI tracing enable/disable
my $long_readlength = 10000; # maximum number of bytes for character
and binary large object types data inserted into database - increase
this value as needed
my $number_of_results = 0; # The actual number of rows deleted from
the database
my $global_database_error = 0;
my $delete_script_posting_url = 'http://www.dotcomsolutionsinc.net/cgi-bin/example_delete_record_display1';
# The script URL which is allowed to POST to this script
my $actual_referred_url = ''; # The URL of the script which POSTed the
delete request - must match this server for security reasons
my $primary_key_column = 'item';
my $plural_error_text = 's'; # Construct correct English error messages.
my $generic_error_text = 'Error encountered:';
my $no_records_found_error_text = 'Record not deleted.';
my $use_external_html = 0; # 1 = read HTML from external files, 0 =
use default HTML
my $external_html_header_filename = 'example_header1.html';
my $external_html_footer_filename = 'example_footer1.html';
# ---------- Alternate HTML Header/Footer -----------
# This HTML page Header/Footer info is used by default
my $html_header =<<"EOF"; # default HTML header
<html>
<head>
<title>example Delete One Record</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000" >
EOF
my $html_footer =<<"EOF"; # default HTML footer
</body>
</html>
EOF
if ($use_external_html == 1)
{
{
# set input line separator to undef to read whole file at once
local $/ = undef;
# read HTML header from external file
open (FILE1,"$external_html_header_filename") || warn ("Could
not open input file $external_html_header_filename for reading. Using
default header HTML instead of external file.");
$html_header = <FILE1>;
# close the input file
close (FILE1);
# read HTML footer from external file
open (FILE1,"$external_html_footer_filename") || warn ("Could
not open input file $external_html_footer_filename for reading. Using
default footer HTML instead of external file.");
$html_footer = <FILE1>;
# close the input file
close (FILE1);
}
}
# ---------- Get Record Number To Delete -----------
# get primary key record number passed in via HTTP POST
my $primary_key_data = param("$primary_key_column");
# ---------- Delete 1 Record From Database -----------
print header();
print $html_header;
if ($debug == 1)
{
# turn on DBI tracing
unlink 'dbitrace.log' if -e 'dbitrace.log';
DBI->trace(2, 'dbitrace.log');
}
my $db_dbh = DBI->connect ("dbi:mysql:$db_connect_string",
"$schema_name", "$schema_password", {RaiseError
=> 0, PrintError => 1, AutoCommit => 1 })
or die "Can't connect to the MySQL $db_connect_string database:
$DBI::errstr\n";
$db_dbh->{LongReadLen} = $long_readlength;
$db_dbh->{LongTruncOk} = 0;
$db_dbh->do("SET OPTION SQL_BIG_TABLES =
1");
my $db_sth = '';
$db_sth = $db_dbh->prepare("delete from
$database_tablename where $primary_key_column = '$primary_key_data'")
or warn " Database error:",$db_dbh->errstr(), "\n";;
$global_database_error = 1 if ($db_sth->err());
# ---------- Validate Referer -----------
# Verify that the data was posted from a trusted source
# for security reasons
$actual_referred_url = CGI::referer();
my $correct_post_url_length = length($delete_script_posting_url);
my $actual_referred_url_shortened = substr($actual_referred_url,0,$correct_post_url_length);
if ($delete_script_posting_url =~ $actual_referred_url_shortened)
{
# referer validation passed - delete the record
$db_sth->execute() or warn $db_sth->errstr(); # check for error
$global_database_error = 1 if ($db_sth->err());
}
else
{
# referer validation failed - do nothing
print "Info not posted by correct website URL: http://www.dotcomsolutionsinc.net/cgi-bin/example_delete_record_display1
<BR>";
}
# ---------- Display Number of Records Deleted -----------
if ($global_database_error == 0)
{
$plural_error_text = '' if $number_of_results == 1;
print "Record deleted.<BR>";
print $html_footer;
}
else
{
if ($global_database_error == 0)
{
print "$no_records_found_error_text<BR>";
print $html_footer;
}
else
{
print "<BR>$generic_error_text<BR>$DBI::errstr";
print $html_footer;
}
}
$db_sth->finish();
# disconnect from database
$db_dbh->disconnect or warn "Can't disconnect from the database
$db_connect_string database: $DBI::errstr\n";
