Perl CGI with SQLite Windows and IIS

RedJeep has asked for the wisdom of the Perl Monks concerning the following question:

Hello. Looking for some guidance. I am creating a very simple site. It is Perl 5. Using CGI with SQLite3 on Windows 10 with IIS 10. I have a simple HTML form that does a post of text field data to a .PL. I did set the database directory to have rights of Full Control. The issue, per the error message, has something to do with rights.

The error I get from IIS is...

HTTP Error 502.2 - Bad Gateway The specified CGI application misbehaved by not returning a complete set of HTTP headers. The headers it did return are "DBD::SQLite::db do failed: attempt to write a readonly database at C:\inetpub\wwwroot\formtest_3.pl line 31. ".

The .PL file is...

#!/usr/bin/perl
    
use CGI qw(:cgi-lib :standard);  
use DBI;
use strict;
use warnings;
 
my $dbfile = "/inetpub/wwwroot/data/sample.db"; 
 
my $dsn      = "dbi:SQLite:dbname=$dbfile";
my $user     = "";
my $password = "";
my $dbh = DBI->connect($dsn, $user, $password, {
   PrintError       => 0,
   RaiseError       => 1,
   AutoCommit       => 1,
   FetchHashKeyName => 'NAME_lc',
    });

### Parse form data    

&ReadParse(my %in);              
    
    my $fname = $in{"fname"};    
    my $lname = $in{"lname"};
    my $user_id = $in{"user_id"};
    my $email = $in{"email"};
    
    
### Write SQL data
$dbh->do('INSERT INTO people (fname, lname, user_id, email) VALUES (?,
+ ?, ?, ?)',
  undef,
  $fname, $lname, $user_id, $email);
  

$dbh->disconnect;

### Print HTML document
my $html = "Content-Type: text/html

<HTML>
<HEAD>
<TITLE>CGI Test</TITLE>
</HEAD>
<BODY>
<H4>CGI Test r1</H4>
<P>
fname: $fname
<p>
lname: $lname
<P>
user_id: $user_id
<p>
email: $email
<p>

</BODY>
</HTML>";

print $html;
[download]

Comment on Perl CGI with SQLite Windows and IIS Download Code

Replies are listed 'Best First'.
Re: Perl CGI with SQLite Windows and IIS by huck (Prior) on Mar 19, 2018 at 19:55 UTC
For starters you may wish to add `use CGI::Carp qw(fatalsToBrowser);` [download] to get past the bad headers part. but i suspect that your problem is that when you said I did set the database directory to have rights of Full Control. one of two things were still not right. first while the directory may have write privileges it may not have also changed all the files in that directory to have write privileges. but it is more likely that you set up the privileges for your user, while IIS does not run under your userid, instead it runs under another userid. and that userid needs to have the write privileges as well as your userid	[reply] [d/l]
Re^2: Perl CGI with SQLite Windows and IIS by RedJeep (Sexton) on Mar 19, 2018 at 20:11 UTC
@huck, Thanks for the reply. I checked and the database file, sample.db, currently has full control for user IIS_IUSERS. I appreciate the help! Any other ideas on what to check?	[reply]
Re^3: Perl CGI with SQLite Windows and IIS by poj (Abbot) on Mar 19, 2018 at 21:25 UTC
Write a small test program `#!/usr/bin/perl use strict; use warnings; use CGI qw(:standard); use CGI::Carp 'fatalsToBrowser'; my $dir = "c:/inetpub/wwwroot/data"; print header('text/plain'); if ( open my $fh,'>',$dir.'/write.txt' ){ print "OK : write.txt created in $dir"; close $fh; } else { print "ERROR : No write permission to $dir" }` [download] poj	[reply] [d/l]
Re^4: Perl CGI with SQLite Windows and IIS by RedJeep (Sexton) on Mar 19, 2018 at 22:46 UTC
Re: Perl CGI with SQLite Windows and IIS by marto (Cardinal) on Mar 20, 2018 at 11:35 UTC
"I am creating a very simple site. It is Perl 5. Using CGI" The documentation states: "CGI.pm is no longer considered good practice for developing web applications, including quick prototyping and small web scripts. There are far better, cleaner, quicker, easier, safer, more scalable, more extensible, more modern alternatives available at this point in time. These will be documented with CGI::Alternatives." Make web development fun again, I suggest investigating Mojolicious::Lite.	[reply]
Re: Perl CGI with SQLite Windows and IIS by bliako (Abbot) on Mar 20, 2018 at 11:25 UTC
Irrelevant to the DB issue but may make debugging easier: In your CGI program always print the HTTP headers before you do anything else. `print "Content-Type: text/html\r\n\r\n";` [download] in this way, any print statement (to STDOUT) from you or from a module you are calling will end up in the browser. Failure to print headers results in server response error 500 when anyone prints to STDOUT an innocent debug message. Another helpful thing will be to redirect STDERR to a server-side log file using: `if( ! open(STDERR, '>>', '/tmp/log.txt') ){ ... }` [download] In my experience, one may control own program's print statements but not 3rd party modules'. Poj's suggestion for using `use CGI::Carp 'fatalsToBrowser';` covers some of the cases I mentioned As per the DB problem in particular: you have to make sure that the DB itself but also the directory it resides in are writable by the OWNER-USER OF THE CGI script. Lastly, multiple access to a DB will initiate file-locking making the DB temporarily read-only. I am not acquainted with SQLite but there may be stale lock files somewhere?	[reply] [d/l] [select]