Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Perl CGI with SQLite Windows and IIS

by RedJeep (Sexton)
on Mar 19, 2018 at 19:19 UTC ( #1211263=perlquestion: print w/replies, xml ) Need Help??

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\ 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;

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);
    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

      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?

        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" }
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:

    " 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.

Re: Perl CGI with SQLite Windows and IIS
by bliako (Monsignor) 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";

    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') ){ ... }

    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?

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1211263]
Approved by stevieb
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2022-12-03 19:47 GMT
Find Nodes?
    Voting Booth?

    No recent polls found