Delete an Entry from DB-Help

[aniwebapp]

I searched in many tutorials and forums. Can any one help me to develop a simple code to delete an entry from a mysql database..

username password

johnson asdfg123

philips 123456

I need cgi/perl script which accepts the username value from an html form and deletes the entry in the DB.

[FishMonger]

Have you read the documentation for the DBI and DBD:mysql modules, which includes examples?
http://search.cpan.org/~timb/DBI-1.607/DBI.pm
http://search.cpan.org/~capttofu/DBD-mysql-4.011/lib/DBD/mysql.pm

What part are having trouble with, the retrieving the form submission or connecting to the db, or executing the sql delete?

Please post the code you've tried.

[aniwebapp]

#!/usr/bin/perl
use DBI;
require "connection_strings.cgi";

print "Content-type: text/html\n\n" ;
$requestor = "$ENV{'REMOTE_USER'}";

$query = "insert into ipm(username, passwd, environ, deposit, requestor) values(";

read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);

foreach $pair (@pairs)
{
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$query .= "\"$value\"";

$query .= ",";

if ($name eq "username")
{
$uname = $value;
}
}

$query .= "\"$requestor\")";

$dbh = DBI->connect($connectInfo,$userid,$passwd) or die DBI->errstr();
$sth = $dbh->prepare($query);
$sth->execute;


this is the code i am using to add info to the db. As I am new to this perl script, i dont know how to go to delete an entry from DB. from the html form, username and environment are passed. so the code should be, "delete from environment where username=". It would be great help if some one can help me in this.

[FishMonger]

The first setp would be to add the warnings and strict pragmas and the CGI module.

use warnings;
use strict;
use CGI qw/:standard/;


Next would be to delete this:
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);

foreach $pair (@pairs)
{
($name, $value) = split(/=/, $pair);
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$query .= "\"$value\"";

$query .= ",";

if ($name eq "username")
{
$uname = $value;
}
}

and replace it with this:
my $uname = param("username");

Your sql insert statement is specifying 5 fields, but you're only passing in 1 value. I doubt that's what you intended.

Does this look like what you want?
#!/usr/bin/perl

use warnings;
use strict;
use DBI qw/:standard/;
require "connection_strings.cgi";

print header(), start_html();

my $uname = param("username");
my $requestor = $ENV{'REMOTE_USER'};

my $dbh = DBI->connect($connectInfo,$userid,$passwd, { RaiseError => 1 })
or die DBI->errstr();

# my $sql = "insert into ipm(username, passwd, environ, deposit, requestor) values(?,?,?,?,?)";


my $sth = $dbh->prepare("delete from ipm where username = ?");
$sth->execute($uname);

[aniwebapp]

Thanks for your reply...

Following is the code which passes the value to my delete_db.pl script. This script accepts value from an html form.

#!/usr/bin/perl
print "Content-type: text/html\n\n" ;
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
#@values = split(/&/,$ENV{QUERY_STRING});
@values = split(/&/,$buffer);
$userpair=shift(@values);
$oupair=shift(@values);
@user_arr = split(/=/, $userpair);
@OU_arr = split(/=/, $oupair);
$user=pop(@user_arr);
$OU=pop(@OU_arr);

system "sudo ./delete_db.pl $OU $user";
exit 0;

i modified the delete_db.pl in the following way.. Through command prompt it is deleting the DB entry. but when i call this through the above script, the script is running but the value is not getting deleted. Please help me to sort out the issue.

#!/usr/bin/perl

use DBI;
require "connection_strings.cgi";

print "Content-type: text/html\n\n" ;

my $uname = $ARGV[1];
my $environ = $ARGV[0];
$query = "delete from $environ where username=\'$uname\'";
#print $query;

$dbh = DBI->connect($connectInfo,$userid,$passwd);
$sth = $dbh->prepare($query);
$sth->execute() or die $dbh->errmsg();

[FishMonger]

Why use 3 scripts when all you need is 1 simple script? Using your 3 scripts only serves to make it more difficult without a good reason.

Combine all 3 scripts and have your form point to that 1 script.

#!/usr/bin/perl

use warnings;
use strict;
use DBI qw/:standard/;

print header(), start_html();

my $connectInfo = 'DBI:mysql:dbname:server';
my $userid = 'dbuser';
my $passwd = 'dbpasswrd';
my $requestor = $ENV{'REMOTE_USER'};

my $uname = param('username');
my $environ = param('environment');

my $dbh = DBI->connect($connectInfo,$userid,$passwd, { RaiseError => 1 })
or die DBI->errstr();

my $sth = $dbh->prepare("delete from $environ where username = ?");

$sth->execute($uname);

# output whatever else you need, then

print end_html();

Note that the script is lacking proper form validation and error handling on the user supplied info.

[aniwebapp]

Thanks a lot for your help :-)