perl script to query database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Icecrack
    Recognized Expert New Member
    • Sep 2008
    • 174

    #31
    Originally posted by happyse27
    Hi Gentlemen,

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


    Thanks,
    Andrew

    Append:

    Code:
    $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:
    Code:
     $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello6", "passhello6", '$email_address', '$filename' ) } ); 
       $sql->execute(); 
     
    while (my @data = $sql->fetchrow_array()) { 
     
          # Print the date from the first four columns in the table 
          print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>"; 
     
    }

    Because this is an insert statement:

    Code:
    $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:

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

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

    try

    Code:
    $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello636", "passhello636", "$email_address", "$filename") } ); 
       $sql->execute() or die "$dbh->errstr\n";; 
    
    $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
    
    while (my @data = $sql->fetchrow_array->execute()) { 
     
          # Print the date from the first four columns in the table 
          print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>";
    Last edited by Icecrack; Oct 10 '08, 01:33 AM. Reason: Many Changes

    Comment

    • happyse27
      New Member
      • Sep 2008
      • 236

      #32
      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

      Code:
      #!c:\perl\bin\perl.exe 
      #!/usr/bin/perl -T 
      use strict; 
      use warnings; 
      use CGI; 
      use CGI::Carp qw(fatalsToBrowser); 
      use File::Basename; 
      use DBD::mysql;
      use DBI; 
        
        
      $CGI::POST_MAX = 1024 * 5000; #adjust as needed (1024 * 5000 = 5MB) 
      $CGI::DISABLE_UPLOADS = 0; #1 disables uploads, 0 enables uploads 
        
      my $query = CGI->new; 
        
      unless ($CGI::VERSION >= 2.47) {  
         error('Your version of CGI.pm is too old. You must have verison 2.47 or higher to use this script.') 
      } 
        
      
      #SECTION B
      #=========================================
      #Database initialization and initial query
      my ($dbh, $data_source);  
      my $mysql_server_name   = 'localhost';  
      my $mysql_database_name = 'test';  
      my $mysql_user_name     = 'root';  
      my $mysql_password      = 'mysql1';  
      
      my $data_source = 'DBI:mysql:' . $mysql_database_name . ':' . $mysql_server_name;  
      my $dbh = DBI->connect( $data_source, $mysql_user_name, $mysql_password, {RaiseError=>1}) ||  die "$DBI::errstr";   
        
        
      my $sql = $dbh->prepare(qq{SELECT * FROM samples}); 
         $sql->execute(); 
      
      while (my @data = $sql->fetchrow_array()) { 
        
            # Print the date from the first two columns in the table 
            print $data[0], "\t", $data[1], "<br>"; 
      } 
      
      1;
      
      # End of Database initial Query
      #==========================================
      
      
       
      
      my $upload_dir = 'D:/Program Files/Apache Software Foundation/Apache2.2/htdocs/upload'; 
        
      # a list of valid characters that can be in filenames 
      my $filename_characters = 'a-zA-Z0-9_.-'; 
        
      my $file = $query->param("photo") or error('No file selected for upload.') ; 
      my $email_address = $query->param("email") || 'Annonymous'; 
        
      # get the filename and the file extension 
      # this could be used to filter out unwanted filetypes 
      # see the File::Basename documentation for details 
      my ($filename,undef,$ext) = fileparse($file,qr{\..*}); 
        
      # append extension to filename 
      $filename .= $ext; 
        
      # convert spaces to underscores "_" 
      $filename =~ tr/ /_/; 
        
      # remove illegal characters 
      $filename =~ s/[^$filename_characters]//g; 
        
      # satisfy taint checking 
      if ($filename =~ /^([$filename_characters]+)$/) { 
         $filename = $1; 
      } 
      else{ 
         error("The filename is not valid. Filenames can only contain these characters: $filename_characters") 
      } 
        
      # this is very crude but validating an email address is not an easy task 
      # and is beyond the scope of this article. To validate an email 
      # address properly use the Emaill::Valid module. I do not include 
      # it here because it is not a core module. 
      unless ($email_address =~ /^[\w@.-]+$/ && length $email_address < 250) { 
         error("The email address appears invalid or contains too many characters. Limit is 250 characters.") 
      }     
        
      my $upload_filehandle = $query->upload("photo"); 
        
      open (UPLOADFILE, ">$upload_dir/$filename") or error($!); 
      binmode UPLOADFILE; 
      while ( <$upload_filehandle> ) { 
         print UPLOADFILE; 
      } 
      close UPLOADFILE; 
      
      
      print $query->header(), 
            $query->start_html(-title=>'Upload Successful'), 
            $query->p('Thanks for uploading your photo!'), 
            $query->p("Your email address: $email_address"), 
            $query->p("Your photo $filename:"), 
            $query->img({src=>"../upload/$filename",alt=>''}), 
            $query->end_html; 
        
        
      sub error { 
         print $query->header(), 
               $query->start_html(-title=>'Error'), 
               shift, 
               $query->end_html; 
         exit(0); 
      } 
      
      
         $sql = $dbh->prepare(qq{insert into samples(username, password, email_address, filename) values ( "userhello636", "passhello636", "$email_address", "$filename") } );  
         $sql->execute() or die "$dbh->errstr\n";;  
        
      $sql = $dbh->prepare(qq{SELECT * FROM samples});  
       $sql->execute(); 
        
      
      while (my @data = $sql->fetchrow_array()){  
        
            # Print the date from the first four columns in the table  
            print $data[0], "\t", $data[1], "\t", $data[2], "\t", $data[3], "<br>";  
      
      }
      1;

      Comment

      Working...