By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,476 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

perl script to query database

100+
P: 236
Hi All,

I am doing a connection to database from perl to mysql via apache. Apache is configured correctly.

Mysql username is root and password is mysql1, and database name is test.

when I type in url 127.0.0.1/cgi-bin/dbconn.pl, it showed internal server error 500. And errors in apache section a) below and errors in access. The samples table is created under test, residing in mysql database.

Tried to triple check and the configurations looks fine. Anyone can kindly assist? Thanks in advance



Andrew


dbconn.pl
Expand|Select|Wrap|Line Numbers
  1.  
  2. #!c:\perl\bin\perl.exe -w
  3. #!/usr/bin/perl -w
  4.  
  5. use DBI;
  6. $dbh = DBI->connect('dbi:mysql:test','root','mysql1')
  7. or die "Connection Error: $DBI::errstr\n";
  8. $sql = "select * from samples";
  9. $sth = $dbh->prepare($sql);
  10. $sth->execute
  11. or die "SQL Error: $DBI::errstr\n";
  12. while (@row = $sth->fetchrow_array) {
  13. print "@row\n";
  14.  
  15.  
Expand|Select|Wrap|Line Numbers
  1. a) apache error log
  2. [Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] Premature end of script headers: dbconn.pl
  3. [Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: C:/perl/site/lib C:/perl/lib .) at (eval 4) line 3.\r
  4. [Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] Perhaps the DBD::mysql perl module hasn't been fully installed,\r
  5. [Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] or perhaps the capitalisation of 'mysql' isn't right.\r
  6. [Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1] Available drivers: CSV, DBM, ExampleP, File, Gofer, ODBC, Oracle, Proxy, SQLite, Sponge.\r
  7. [Mon Oct 06 01:07:39 2008] [error] [client 127.0.0.1]  at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/dbconn.pl line 5\r
Oct 5 '08 #1
Share this Question
Share on Google+
31 Replies


eWish
Expert 100+
P: 971
You will need to print the headers before you try and print anything to the screen. Add this line of code to your script.
Expand|Select|Wrap|Line Numbers
  1. print "Content-type: text/html", "\n\n";
If you are using the CGI.pm then you can do the following.
Expand|Select|Wrap|Line Numbers
  1. use CGI;
  2. my $q = CGI->new();
  3.  
  4. print $q->header();
Also to help debug you can add the following as well.
Expand|Select|Wrap|Line Numbers
  1. use CGI::Carp qw/fatalsToBrowser/;
Edit: It appears that you don't have DBD::Mysql installed. This a must when you are connecting to a MySQL DB.
--Kevin
Oct 5 '08 #2

eWish
Expert 100+
P: 971
Here is a link to a resource to help determining some of the common problems when you get the dreaded 500 error.

--Kevin
Oct 5 '08 #3

100+
P: 236
Hi Kevin,

Thanks again. Will try out what you mentioned...

I was using this link http://perl.about.com/od/perltutorials/a/perlmysql_3.htm



Cheers...
Andrew
Oct 6 '08 #4

100+
P: 236
Hi...

I tried out the below script, this time the url 127.0.0.1/cgi-bin/dbconn.pl printed out blank screen(no more screen error). I had installed dbd-mysqlpp.

Apache error log showed 500 and 543 errors. Apache access log showed 127.0.0.1 - - [06/Oct/2008:08:35:18 -0700] "GET /cgi-bin/dbconn.pl HTTP/1.1" 200 -

Kindly assist. I will however try some other methods further.

Btw, what is CGI PM? Also, the print "Content-type: text/html", "\n\n"; which line I should put in?


Thanks and Best Rgds,
Andrew

Expand|Select|Wrap|Line Numbers
  1.  
  2. #!c:\perl\bin\perl.exe -w
  3. #!/usr/bin/perl -w
  4.  
  5.  
  6. use DBI;
  7.  
  8. use CGI; 
  9. my $q = CGI->new(); 
  10.  
  11. print $q->header(); 
  12.  
  13.  
  14. $dbh = DBI->connect('dbi:mysql:test','root','mysql1')
  15. or die "Connection Error: $DBI::errstr\n";
  16. $sql = "select * from samples";
  17. $sth = $dbh->prepare($sql);
  18. $sth->execute
  19. or die "SQL Error: $DBI::errstr\n";
  20. print "Content-type: text/html", "\n\n"; 
  21.  
  22. while (@row = $sth->fetchrow_array) {
  23. print "@row\n";
  24.  
  25.  
  26.  
  27.  
Oct 6 '08 #5

KevinADC
Expert 2.5K+
P: 4,059
try changing 'root' to 'localhost'
Oct 6 '08 #6

eWish
Expert 100+
P: 971
I did not even look at the connection string. Here is an example of a connection string that works.

Expand|Select|Wrap|Line Numbers
  1. my ($dbh, $data_source);
  2. my $mysql_server_name   = 'localhost';
  3. my $mysql_database_name = 'xxx';
  4. my $mysql_user_name     = 'root';
  5. my $mysql_password      = 'xxx';
  6.  
  7. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;
  8. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {PrintError=>1}) ||  die "$DBI::errstr"; 
--Kevin
Oct 6 '08 #7

P: 4
Hi,

Make sure that there are entries there in the database, otherwise the result would be a blank screen again.
search for Mysql+connect +perl in google and you get thousands of snippets which you can use.
Oct 6 '08 #8

100+
P: 236
Hi All,

I tried both methods and it show the content type Content-type: text/html in the url 127.0.0.1/cgi-bin/dbconn2.pl. And alternately it shows internal error.

apache access show status 200 ok, but it showed error 25.

my database is test, user and password correct. It showed root@localhost, with mysql, test db tables. both mysql and test got the same samples rows.

What could be wrong, tried many different ways and scratching head whole nite. Thanks.

Code below :

Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe -w
  2. #!/usr/bin/perl -w
  3.  
  4. use DBI;
  5. use mysqlpp;
  6. use apachemysql;
  7.  
  8. print "Content-type: text/html", "\n\n";
  9.  
  10. my ($dbh, $data_source); 
  11. my $mysql_server_name   = 'localhost'; 
  12. my $mysql_database_name = 'test'; 
  13. my $mysql_user_name     = 'root'; 
  14. my $mysql_password      = 'mysql1'; 
  15.  
  16. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name; 
  17. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {PrintError=>1}) ||  die "$DBI::errstr";  
  18.  
  19.  
  20. $sql = "select * from samples";
  21. $sth = $dbh->prepare($sql);
  22. $sth->execute
  23. or die "SQL Error: $DBI::errstr\n";
  24.  
  25. print "testing output : $sth ";
  26.  print "Press the ENTER key to exit program ..."; 
  27.  $pause = <STDIN>;  #Like a PAUSE statement in DOS .bat files 
  28.  
  29.  exit; 
  30.  
  31.  
Oct 6 '08 #9

eWish
Expert 100+
P: 971
Here is your code reworked a bit. Be sure to check out the DBI documentation if you see things you don't understand on the DBI part of the script. This script is also assuming that you are accessing it via a web browser.

Expand|Select|Wrap|Line Numbers
  1. #! /usr/bin/perl -T
  2.  
  3. use strict;
  4. use warnings;
  5.  
  6. use DBI;
  7. use CGI::Carp qw/fatalsToBrowser/;
  8.  
  9. print "Content-type: text/html", "\n\n";
  10.  
  11. my ($dbh, $data_source); 
  12. my $mysql_server_name   = 'localhost'; 
  13. my $mysql_database_name = 'test'; 
  14. my $mysql_user_name     = 'root'; 
  15. my $mysql_password      = ''; 
  16.  
  17. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name; 
  18. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";  
  19.  
  20.  
  21. my $sql = $dbh->prepare(qq{SELECT * FROM samples});
  22.    $sql->execute();
  23.  
  24. while (my @data = $sql->fetchrow_array()) {
  25.  
  26.       # Print the date from the first two columns in the table
  27.       print $data[0], "\t", $data[1], "<br>";
  28. }
  29.  
  30.  
  31. 1;
--Kevin
Oct 6 '08 #10

100+
P: 236
Hi Guru Kevin,

Thanks. But Still Cannot Work after trying. got apache Errors 500 and 543. Also, the url 127.0.0.1/cgi-bin/db.pl showed is :

Software error:
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: C:/perl/site/lib C:/perl/lib .) at (eval 5) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: CSV, DBM, ExampleP, File, Gofer, ODBC, Oracle, Proxy, SQLite, Sponge, mysqlPP.
at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/db.pl line 19

For help, please send mail to the webmaster (admin@gateway.2wire.net), giving this error message and the time and date of the error.



Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe
  2. #!/usr/bin/perl -T 
  3.  
  4. use strict; 
  5. use warnings; 
  6. use mysqlpp;
  7.  
  8. use DBI; 
  9. use CGI::Carp qw(fatalsToBrowser); 
  10.  
  11. print "Content-type: text/html", "\n\n"; 
  12.  
  13. my ($dbh, $data_source);  
  14. my $mysql_server_name   = 'localhost';  
  15. my $mysql_database_name = 'test';  
  16. my $mysql_user_name     = 'root';  
  17. my $mysql_password      = 'mysql1';  
  18.  
  19. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  20. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  21.  
  22.  
  23. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  24.    $sql->execute(); 
  25.  
  26. while (my @data = $sql->fetchrow_array()) { 
  27.  
  28.       # Print the date from the first two columns in the table 
  29.       print $data[0], "\t", $data[1], "<br>"; 
  30.  
  31.  
  32. 1; 
  33.  
  34.  
  35.  
Oct 6 '08 #11

100+
P: 236
I am using windows perl and windows apache btw. Cheers...
Oct 6 '08 #12

eWish
Expert 100+
P: 971
Test the script with this line omitted or commented out. Just to see what happens.

Expand|Select|Wrap|Line Numbers
  1. use mysqlpp;

Also, run this script. What it will do is tell you if you have the DBI and DBD::mysql modules are installed and the version number of each. It really appears that you do not have the DBD::mysql module installed.

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl
  2.  
  3. use strict;
  4. use warnings;
  5.  
  6. use CGI;
  7. use CGI::Carp qw/fatalsToBrowser/;
  8. use DBI;
  9. use DBD::mysql;
  10.  
  11. my $q = CGI->new;
  12.  
  13. print $q->header();
  14. print "$ENV{'DOCUMENT_ROOT'} has DBI Version: " .$DBI::VERSION. " installed on it.<br>";
  15. print "$ENV{'DOCUMENT_ROOT'} has DBD::mysql Version: " .$DBD::mysql::VERSION. " installed on it.<br>";
  16.  
  17.  
  18. 1;
--Kevin
Oct 7 '08 #13

KevinADC
Expert 2.5K+
P: 4,059
As mentioned previously in this thread, you need to install DBD::Mysql
Oct 7 '08 #14

KevinADC
Expert 2.5K+
P: 4,059

100+
P: 236
Hi Kevin,

scan through and searched few times...I cant find this module DBD::Mysql, i can only install mysqlpp module. It is strange...

Thanks,
Andrew
Oct 7 '08 #16

KevinADC
Expert 2.5K+
P: 4,059
Hi Kevin,

scan through and searched few times...I cant find this module DBD::Mysql, i can only install mysqlpp module. It is strange...

Thanks,
Andrew
Did you read the thread I linked for you?
Oct 7 '08 #17

100+
P: 236
Hi Kevin,

Thanks again. I read throught the thread you gave. And it is working now.
http://bytes.com/forum/thread840298...3A%3Amysql.html Installed mysql module, the exact name is use dbd::mysql in windows perl script. Cheers!!! again.

Try this:

START>>PROGRAMS>>ACTIVEPERL>>PPM

Once thePPM GUI is running click on the EDIT tab, then PREFERENCES, then click on the REPOSITORIES tab then on the SUGGESTED tab select "uwinnipeg" and click on ADD. After the uwinnipeg packages are viewable see if DBD-mysql is listed. If not try the same thing and add "bribes" to the list of repositories. Once the DBD-mysql module is listed mark it for install then install it. Read the PPM help files if my instructions are confusing.

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

complete code
Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe
  2. #!/usr/bin/perl -T 
  3.  
  4. use strict; 
  5. use DBD::mysql;
  6. use DBI; 
  7. use CGI::Carp qw(fatalsToBrowser); 
  8.  
  9. print "Content-type: text/html", "\n\n"; 
  10.  
  11. my ($dbh, $data_source);  
  12. my $mysql_server_name   = 'localhost';  
  13. my $mysql_database_name = 'test';  
  14. my $mysql_user_name     = 'root';  
  15. my $mysql_password      = 'mysql1';  
  16.  
  17. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  18. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  19.  
  20.  
  21. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  22.    $sql->execute(); 
  23.  
  24. while (my @data = $sql->fetchrow_array()) { 
  25.  
  26.       # Print the date from the first two columns in the table 
  27.       print $data[0], "\t", $data[1], "<br>"; 
  28.  
  29.  
  30. 1; 
  31.  
Oct 7 '08 #18

KevinADC
Expert 2.5K+
P: 4,059
Good. In the future please read what people post and try and follow any instructions you are given. Sometimes you are not reading all the replies or doing what people ask. Since we are trying to help you, you need to try and follow instructions and any other requests or advise posted.

Regards,
Kevin
Oct 7 '08 #19

100+
P: 236
Hi...

sorry Kevin, I missed out the instructions sometimes as I do panic and scramble for more information. Will be more attentive. if not missed the instruction I also wanted and would have wanted to resolve the matter earlier, alot of time wasted in resolving bugs...

Btw, if I use to use OOP(object based programming), which are the things to look out for to install, which references sites to read and what is the way to make sure I can re-use codes...


Cheers...
Andrew
Oct 8 '08 #20

100+
P: 236
Hi All,

Two questions to consult...

1) I can insert database information successfully using insert below in the code(see code below).

2) but I cant display any output in html. Any idea? I got the error below (see section a below).

The script which part is going wrong? Also the 1; not sure how to resolve? Thanks in advance again.


Cheers...
Andrew


a) Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, admin@gateway.2wire.net and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Expand|Select|Wrap|Line Numbers
  1.  
  2. #!c:\perl\bin\perl.exe 
  3. #!/usr/bin/perl -T 
  4.  
  5. use strict; 
  6. use warnings; 
  7. use CGI; 
  8. use CGI::Carp qw/fatalsToBrowser/; 
  9. use File::Basename; 
  10.  
  11. use DBD::mysql;
  12. use DBI; 
  13.  
  14.  
  15. $CGI::POST_MAX = 1024 * 5000; #adjust as needed (1024 * 5000 = 5MB) 
  16. $CGI::DISABLE_UPLOADS = 0; #1 disables uploads, 0 enables uploads 
  17.  
  18. my $query = CGI->new; 
  19.  
  20. unless ($CGI::VERSION >= 2.47) {  
  21.    error('Your version of CGI.pm is too old. You must have verison 2.47 or higher to use this script.') 
  22.  
  23.  
  24. #SECTION B
  25. #=========================================
  26. #Database initialization and initial query
  27. my ($dbh, $data_source);  
  28. my $mysql_server_name   = 'localhost';  
  29. my $mysql_database_name = 'test';  
  30. my $mysql_user_name     = 'root';  
  31. my $mysql_password      = 'mysql1';  
  32.  
  33. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  34. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  35.  
  36.  
  37. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  38.    $sql->execute(); 
  39.  
  40. while (my @data = $sql->fetchrow_array()) { 
  41.  
  42.       # Print the date from the first two columns in the table 
  43.       print $data[0], "\t", $data[1], "<br>"; 
  44.  
  45. 1;
  46.  
  47. # End of Database initial Query
  48. #==========================================
  49.  
  50.  
  51.  
  52.  
  53. my $upload_dir = 'D:/Program Files/Apache Software Foundation/Apache2.2/htdocs/upload'; 
  54.  
  55. # a list of valid characters that can be in filenames 
  56. my $filename_characters = 'a-zA-Z0-9_.-'; 
  57.  
  58. my $file = $query->param("photo") or error('No file selected for upload.') ; 
  59. my $email_address = $query->param("email") || 'Annonymous'; 
  60.  
  61. # get the filename and the file extension 
  62. # this could be used to filter out unwanted filetypes 
  63. # see the File::Basename documentation for details 
  64. my ($filename,undef,$ext) = fileparse($file,qr{\..*}); 
  65.  
  66. # append extension to filename 
  67. $filename .= $ext; 
  68.  
  69. # convert spaces to underscores "_" 
  70. $filename =~ tr/ /_/; 
  71.  
  72. # remove illegal characters 
  73. $filename =~ s/[^$filename_characters]//g; 
  74.  
  75. # satisfy taint checking 
  76. if ($filename =~ /^([$filename_characters]+)$/) { 
  77.    $filename = $1; 
  78. else{ 
  79.    error("The filename is not valid. Filenames can only contain these characters: $filename_characters") 
  80.  
  81. # this is very crude but validating an email address is not an easy task 
  82. # and is beyond the scope of this article. To validate an email 
  83. # address properly use the Emaill::Valid module. I do not include 
  84. # it here because it is not a core module. 
  85. unless ($email_address =~ /^[\w@.-]+$/ && length $email_address < 250) { 
  86.    error("The email address appears invalid or contains too many characters. Limit is 250 characters.") 
  87. }     
  88.  
  89. my $upload_filehandle = $query->upload("photo"); 
  90.  
  91. open (UPLOADFILE, ">$upload_dir/$filename") or error($!); 
  92. binmode UPLOADFILE; 
  93. while ( <$upload_filehandle> ) { 
  94.    print UPLOADFILE; 
  95. close UPLOADFILE; 
  96.  
  97. my $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello2", "passhello2", '$email_address', '$filename' ) } ); 
  98.    $sql->execute(); 
  99.  
  100. while (my @data = $sql->fetchrow_array()) { 
  101.  
  102.       # Print the date from the first four columns in the table 
  103.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  104.  
  105. }
  106.  
  107. 1;
  108.  
  109. print $query->header(), 
  110.       $query->start_html(-title=>'Upload Successful'), 
  111.       $query->p('Thanks for uploading your photo!'), 
  112.       $query->p("Your email address: $email_address"), 
  113.       $query->p("Your photo $filename:"), 
  114.       $query->img({src=>"../upload/$filename",alt=>''}), 
  115.       $query->end_html; 
  116.  
  117.  
  118. sub error { 
  119.    print $query->header(), 
  120.          $query->start_html(-title=>'Error'), 
  121.          shift, 
  122.          $query->end_html; 
  123.    exit(0); 
  124.  
  125.  
Oct 8 '08 #21

KevinADC
Expert 2.5K+
P: 4,059
A problem I see in your script is using "my". The only one that will have any impact is here:

Expand|Select|Wrap|Line Numbers
  1. my $sql = $dbh->prepare(qq{SELECT * FROM samples});
later, and more importantly, in the same scope of the $sql variable you have:

Expand|Select|Wrap|Line Numbers
  1. my $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello2", "passhello2", '$email_address', '$filename' ) } ); 
Any previous value $sql had is lost when you redeclare it with "my" in the same scope. So you need to remove "my" from the second line.

I don't think that is the source of the error you get because that error should be trapped by CGI::Carp and printed out to the browser.

This line is not necessary:

Expand|Select|Wrap|Line Numbers
  1. use DBD::mysql; 
I don't know if it will cause a problem to include it but you can remove it and see if that helps resolve any errors. the DBI module knows where to get the driver you need in the connect string.

Remove any blank lines from the beginning of your script (before the shebang line) if there are any.
Oct 8 '08 #22

KevinADC
Expert 2.5K+
P: 4,059
Also, at line 44 of your code there is a print command. But you have not printed an HTTP header first. Since that print command is output to STDOUT it will cause the script to fail.
Oct 8 '08 #23

100+
P: 236
Hi Kevin / all,

Thanks Guru Kevin! I troubleshooted the script and the output is below. But the software error is there, not sure what it is, tried without or with the mysql module "use DBD::mysql;" and taken out my $sql in the insert statement. And partial html output is shown only. Code below is right after the html output. Kindly Advise... Thanks in advance.

The insert statement worked, the html PARTIALLY worked when I shift the html after the printing of the statements and the picture below. Not sure what is wrong, been circling around and still got problem.

Apache access log showed : 127.0.0.1 - - [10/Oct/2008:00:15:17 -0700] "POST /cgi-bin/u2.pl HTTP/1.1" 200 902

And perl -c u2.pl showed :
C:\Perl\bin>perl -c d:u2.pl
[Fri Oct 10 00:20:58 2008] u2.pl: "my" variable $data_source masks earlier decla
ration in same scope at d:u2.pl line 31.
[Fri Oct 10 00:20:58 2008] u2.pl: "my" variable $dbh masks earlier declaration i
n same scope at d:u2.pl line 32.
d:u2.pl syntax OK




html output(including software error below)
===============================
Thanks for uploading your photo!

Your email address: h6@email.com

Your photo E-028.JPG:
[photo is shown]


Software error:
DBD::mysql::st fetchrow_array failed: fetch() without execute() at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/u2.pl line 121, <fh00001D%3A\E-028.JPG> line 313.
For help, please send mail to the webmaster (admin@gateway.2wire.net), giving this error message and the time and date of the error.


Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe 
  2. #!/usr/bin/perl -T 
  3. use strict; 
  4. use warnings; 
  5. use CGI; 
  6. use CGI::Carp qw(fatalsToBrowser); 
  7. use File::Basename; 
  8. use DBD::mysql;
  9. use DBI; 
  10.  
  11.  
  12. $CGI::POST_MAX = 1024 * 5000; #adjust as needed (1024 * 5000 = 5MB) 
  13. $CGI::DISABLE_UPLOADS = 0; #1 disables uploads, 0 enables uploads 
  14.  
  15. my $query = CGI->new; 
  16.  
  17. unless ($CGI::VERSION >= 2.47) {  
  18.    error('Your version of CGI.pm is too old. You must have verison 2.47 or higher to use this script.') 
  19.  
  20.  
  21. #SECTION B
  22. #=========================================
  23. #Database initialization and initial query
  24. my ($dbh, $data_source);  
  25. my $mysql_server_name   = 'localhost';  
  26. my $mysql_database_name = 'test';  
  27. my $mysql_user_name     = 'root';  
  28. my $mysql_password      = 'mysql1';  
  29.  
  30. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  31. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  32.  
  33.  
  34. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  35.    $sql->execute(); 
  36.  
  37. while (my @data = $sql->fetchrow_array()) { 
  38.  
  39.       # Print the date from the first two columns in the table 
  40.       print $data[0], "\t", $data[1], "<br>"; 
  41.  
  42. 1;
  43.  
  44. # End of Database initial Query
  45. #==========================================
  46.  
  47.  
  48.  
  49.  
  50. my $upload_dir = 'D:/Program Files/Apache Software Foundation/Apache2.2/htdocs/upload'; 
  51.  
  52. # a list of valid characters that can be in filenames 
  53. my $filename_characters = 'a-zA-Z0-9_.-'; 
  54.  
  55. my $file = $query->param("photo") or error('No file selected for upload.') ; 
  56. my $email_address = $query->param("email") || 'Annonymous'; 
  57.  
  58. # get the filename and the file extension 
  59. # this could be used to filter out unwanted filetypes 
  60. # see the File::Basename documentation for details 
  61. my ($filename,undef,$ext) = fileparse($file,qr{\..*}); 
  62.  
  63. # append extension to filename 
  64. $filename .= $ext; 
  65.  
  66. # convert spaces to underscores "_" 
  67. $filename =~ tr/ /_/; 
  68.  
  69. # remove illegal characters 
  70. $filename =~ s/[^$filename_characters]//g; 
  71.  
  72. # satisfy taint checking 
  73. if ($filename =~ /^([$filename_characters]+)$/) { 
  74.    $filename = $1; 
  75. else{ 
  76.    error("The filename is not valid. Filenames can only contain these characters: $filename_characters") 
  77.  
  78. # this is very crude but validating an email address is not an easy task 
  79. # and is beyond the scope of this article. To validate an email 
  80. # address properly use the Emaill::Valid module. I do not include 
  81. # it here because it is not a core module. 
  82. unless ($email_address =~ /^[\w@.-]+$/ && length $email_address < 250) { 
  83.    error("The email address appears invalid or contains too many characters. Limit is 250 characters.") 
  84. }     
  85.  
  86. my $upload_filehandle = $query->upload("photo"); 
  87.  
  88. open (UPLOADFILE, ">$upload_dir/$filename") or error($!); 
  89. binmode UPLOADFILE; 
  90. while ( <$upload_filehandle> ) { 
  91.    print UPLOADFILE; 
  92. close UPLOADFILE; 
  93.  
  94.  
  95. print $query->header(), 
  96.       $query->start_html(-title=>'Upload Successful'), 
  97.       $query->p('Thanks for uploading your photo!'), 
  98.       $query->p("Your email address: $email_address"), 
  99.       $query->p("Your photo $filename:"), 
  100.       $query->img({src=>"../upload/$filename",alt=>''}), 
  101.       $query->end_html; 
  102.  
  103.  
  104. sub error { 
  105.    print $query->header(), 
  106.          $query->start_html(-title=>'Error'), 
  107.          shift, 
  108.          $query->end_html; 
  109.    exit(0); 
  110.  
  111.  
  112.    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
  113.    $sql->execute(); 
  114.  
  115. while (my @data = $sql->fetchrow_array()) { 
  116.  
  117.       # Print the date from the first four columns in the table 
  118.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  119.  
  120. }
  121.  
  122.  
  123. 1;
  124.  
  125.  
Oct 9 '08 #24

100+
P: 236
Hi All,

I found this, tried some methods, but still cant work well.

http://www.webmasterworld.com/forum13/3259.htm

Thanks in advance,
Andrew

Change the second statement below :

Expand|Select|Wrap|Line Numbers
  1.  
  2.    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello66", "passhello66", '$email_address', '$filename' ) } ); 
  3.    $sql->execute() or die "$dbh->errstr\n"; 
  4.  
  5. while (my @data = $sql->fetchrow_array()) { 
  6.  
  7.       # Print the date from the first four columns in the table 
  8.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  9.  
  10. }
  11.  
  12.  
Oct 9 '08 #25

KevinADC
Expert 2.5K+
P: 4,059
In this line you have scalars that are single-quoted:

Expand|Select|Wrap|Line Numbers
  1. $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
remove the single-quotes:

Expand|Select|Wrap|Line Numbers
  1. $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", $email_address, $filename ) } ); 
Then retry your code. Scalars do not need to be quoted at all, and single-quotes just kills any variable interpolation anyway.
Oct 9 '08 #26

100+
P: 236
Hi Kevin / All,

Tried and it completely didnt work, only double quote works.. See code below. I am still get same error below when I tried to print out the data in html output although the data got inserted into the database successful., :Kindly assist.

Software error:
DBD::mysql::st fetchrow_array failed: fetch() without execute() at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/u2.pl line 122, <fh00001D%3A\DSC00571-resize.JPG> line 5268.
For help, please send mail to the webmaster (admin@gateway.2wire.net), giving this error message and the time and date of the error.

Expand|Select|Wrap|Line Numbers
  1.  
  2.    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello636", "passhello636", "$email_address", "$filename") } ); 
  3.    $sql->execute() or die "$dbh->errstr\n";; 
  4.  
  5.  
  6. while (my @data = $sql->fetchrow_array()) { 
  7.  
  8.       # Print the date from the first four columns in the table 
  9.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  10.  
  11. }
  12.  
  13.  
  14. 1;
  15.  
  16.  
  17.  
Oct 9 '08 #27

KevinADC
Expert 2.5K+
P: 4,059
Sorry, my suggestion was wrong anyway, the single-quotes were OK in the context you had them because the string is inside a qq{} operator. Its the same as this:

Expand|Select|Wrap|Line Numbers
  1. $foo = 'test';
  2. $str = "this is a '$foo' ";
  3. print $str;
So the single-quotes weren't a problem.
Oct 9 '08 #28

Icecrack
Expert 100+
P: 174
Sorry, my suggestion was wrong anyway, the single-quotes were OK in the context you had them because the string is inside a qq{} operator. Its the same as this:

Expand|Select|Wrap|Line Numbers
  1. $foo = 'test';
  2. $str = "this is a '$foo' ";
  3. print $str;
So the single-quotes weren't a problem.
hey Kevin Stop confusing me there :)

also on the topic

Expand|Select|Wrap|Line Numbers
  1. Software error:
  2. DBD::mysql::st fetchrow_array failed: fetch() without execute() at D:/Program Files/Apache Software Foundation/Apache2.2/cgi-bin/u2.pl line 122, <fh00001D%3A\DSC00571-resize.JPG> line 5268.
its casing an error because SQL hates \ in its input, try to cancel this by \\\\ in perl
try

Expand|Select|Wrap|Line Numbers
  1. $filename=~ s/\/\\/g;
before you write you sql string

also watch out for single quotes ' in SQL,
Oct 10 '08 #29

100+
P: 236
Hi Gentlemen,

Thanks! So Which part should I try to edit? I am confused...


Thanks,
Andrew
Oct 10 '08 #30

Icecrack
Expert 100+
P: 174
Hi Gentlemen,

Thanks! So Which part should I try to edit? I am confused...


Thanks,
Andrew

Append:

Expand|Select|Wrap|Line Numbers
  1. $filename=~ s/\\/\\\\/g;
this to any place in your code where you would read, this would be after the read or this would be before you write $filename

Also

This should not work:
Expand|Select|Wrap|Line Numbers
  1.  $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
  2.    $sql->execute(); 
  3.  
  4. while (my @data = $sql->fetchrow_array()) { 
  5.  
  6.       # Print the date from the first four columns in the table 
  7.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  8.  
  9. }

Because this is an insert statement:

Expand|Select|Wrap|Line Numbers
  1. $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
and this will be calling the insert statement to write and not read:

Expand|Select|Wrap|Line Numbers
  1. while (my @data = $sql->fetchrow_array()) { 
  2.  
  3.       # Print the date from the first four columns in the table 
  4.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  5.  
this is because you have over written $sql with the write statement.

also its complaining about: fetch() without execute()

try

Expand|Select|Wrap|Line Numbers
  1.  
  2. $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello636", "passhello636", "$email_address", "$filename") } ); 
  3.    $sql->execute() or die "$dbh->errstr\n";; 
  4.  
  5. $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  6.  
  7. while (my @data = $sql->fetchrow_array->execute()) { 
  8.  
  9.       # Print the date from the first four columns in the table 
  10.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
  11.  
Oct 10 '08 #31

100+
P: 236
Hi Sir Icecrack,

I understand now. Thanks Again. Used your final part except one statement missing which I added... The first part not sure what you referring to. This is the code. Cheers...


Thanks,
Andrew

Expand|Select|Wrap|Line Numbers
  1. #!c:\perl\bin\perl.exe 
  2. #!/usr/bin/perl -T 
  3. use strict; 
  4. use warnings; 
  5. use CGI; 
  6. use CGI::Carp qw(fatalsToBrowser); 
  7. use File::Basename; 
  8. use DBD::mysql;
  9. use DBI; 
  10.  
  11.  
  12. $CGI::POST_MAX = 1024 * 5000; #adjust as needed (1024 * 5000 = 5MB) 
  13. $CGI::DISABLE_UPLOADS = 0; #1 disables uploads, 0 enables uploads 
  14.  
  15. my $query = CGI->new; 
  16.  
  17. unless ($CGI::VERSION >= 2.47) {  
  18.    error('Your version of CGI.pm is too old. You must have verison 2.47 or higher to use this script.') 
  19.  
  20.  
  21. #SECTION B
  22. #=========================================
  23. #Database initialization and initial query
  24. my ($dbh, $data_source);  
  25. my $mysql_server_name   = 'localhost';  
  26. my $mysql_database_name = 'test';  
  27. my $mysql_user_name     = 'root';  
  28. my $mysql_password      = 'mysql1';  
  29.  
  30. my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
  31. my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
  32.  
  33.  
  34. my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
  35.    $sql->execute(); 
  36.  
  37. while (my @data = $sql->fetchrow_array()) { 
  38.  
  39.       # Print the date from the first two columns in the table 
  40.       print $data[0], "\t", $data[1], "<br>"; 
  41.  
  42. 1;
  43.  
  44. # End of Database initial Query
  45. #==========================================
  46.  
  47.  
  48.  
  49.  
  50. my $upload_dir = 'D:/Program Files/Apache Software Foundation/Apache2.2/htdocs/upload'; 
  51.  
  52. # a list of valid characters that can be in filenames 
  53. my $filename_characters = 'a-zA-Z0-9_.-'; 
  54.  
  55. my $file = $query->param("photo") or error('No file selected for upload.') ; 
  56. my $email_address = $query->param("email") || 'Annonymous'; 
  57.  
  58. # get the filename and the file extension 
  59. # this could be used to filter out unwanted filetypes 
  60. # see the File::Basename documentation for details 
  61. my ($filename,undef,$ext) = fileparse($file,qr{\..*}); 
  62.  
  63. # append extension to filename 
  64. $filename .= $ext; 
  65.  
  66. # convert spaces to underscores "_" 
  67. $filename =~ tr/ /_/; 
  68.  
  69. # remove illegal characters 
  70. $filename =~ s/[^$filename_characters]//g; 
  71.  
  72. # satisfy taint checking 
  73. if ($filename =~ /^([$filename_characters]+)$/) { 
  74.    $filename = $1; 
  75. else{ 
  76.    error("The filename is not valid. Filenames can only contain these characters: $filename_characters") 
  77.  
  78. # this is very crude but validating an email address is not an easy task 
  79. # and is beyond the scope of this article. To validate an email 
  80. # address properly use the Emaill::Valid module. I do not include 
  81. # it here because it is not a core module. 
  82. unless ($email_address =~ /^[\w@.-]+$/ && length $email_address < 250) { 
  83.    error("The email address appears invalid or contains too many characters. Limit is 250 characters.") 
  84. }     
  85.  
  86. my $upload_filehandle = $query->upload("photo"); 
  87.  
  88. open (UPLOADFILE, ">$upload_dir/$filename") or error($!); 
  89. binmode UPLOADFILE; 
  90. while ( <$upload_filehandle> ) { 
  91.    print UPLOADFILE; 
  92. close UPLOADFILE; 
  93.  
  94.  
  95. print $query->header(), 
  96.       $query->start_html(-title=>'Upload Successful'), 
  97.       $query->p('Thanks for uploading your photo!'), 
  98.       $query->p("Your email address: $email_address"), 
  99.       $query->p("Your photo $filename:"), 
  100.       $query->img({src=>"../upload/$filename",alt=>''}), 
  101.       $query->end_html; 
  102.  
  103.  
  104. sub error { 
  105.    print $query->header(), 
  106.          $query->start_html(-title=>'Error'), 
  107.          shift, 
  108.          $query->end_html; 
  109.    exit(0); 
  110.  
  111.  
  112.    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello636", "passhello636", "$email_address", "$filename") } );  
  113.    $sql->execute() or die "$dbh->errstr\n";;  
  114.  
  115. $sql = $dbh->prepare(qq{SELECT * FROM samples});  
  116.  $sql->execute(); 
  117.  
  118.  
  119. while (my @data = $sql->fetchrow_array()){  
  120.  
  121.       # Print the date from the first four columns in the table  
  122.       print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>";  
  123.  
  124. }
  125. 1;
  126.  
  127.  
Oct 10 '08 #32

Post your reply

Sign in to post your reply or Sign up for a free account.