«

Hi,

I need to be able to input text into an html form that is submitted to
my perl cgi script (using CGI.pm), and subsequently written to a
database (using DBI, mysql). The script will be secured using access
permissions, but 'just in case', I would like to know what security
issues I need to be alert for as far as cgi input sanitization is
concerned.
My problem is, I need to be able to input *any* text to the cgi script
(for all intents and purposes, I'll call the page a 'blog', and the
form+cgi+db serves to allow me to post new content from the web), so
filtering seems moot.
Should I have urlencoded data entered into the database (and urldecoded
before it is written out to the result page)? Do I even need to worry?
I've done as much research as I can (google, perldocs, cgi faqs) but I
am at a stand-still.
example query:
"INSERT INTO (field) VALUES ($cgiobj->param('cgi-parameter'))"; where
'field' is varchar and parameter should be able to include any text.

This issue is absolutely stopping the development of my website right
now.

Thank you in advance!
-Robert

robert.waters wrote:
> I need to be able to input text into an html form that is submitted to
> my perl cgi script (using CGI.pm), and subsequently written to a
> database (using DBI, mysql). The script will be secured using access
> permissions, but 'just in case', I would like to know what security
> issues I need to be alert for as far as cgi input sanitization is
> concerned.
> My problem is, I need to be able to input *any* text to the cgi script
> (for all intents and purposes, I'll call the page a 'blog', and the
> form+cgi+db serves to allow me to post new content from the web), so
> filtering seems moot.
> Should I have urlencoded data entered into the database (and urldecoded
> before it is written out to the result page)?

No. Uriencoding is for transferring data in e.g. HTTP requests.

> Do I even need to worry?

Yes, there is always a need to worry when dealing with CGI.

If you intend to display the data as part of HTML pages, you should
convert certain characters to HTML entities. Since you are using CGI.pm,
check out CGI::escapeHTML(). Personally I usually do that immediately
before printing to STDOUT, i.e. I don't do it before storing data in a
database.

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl

"robert.waters" <> writes:

> I've done as much research as I can (google, perldocs, cgi faqs) but I
> am at a stand-still.

You might want to google for "SQL Injection". Directly interpolating vars
into SQL strings can be problematic - you should use placeholders to help
avoid that. They also give a nice performance boost if you're repeating the
same query with different data.

> example query:
> "INSERT INTO (field) VALUES ($cgiobj->param('cgi-parameter'))"; where
> 'field' is varchar and parameter should be able to include any text.

Using placeholders:

# Assuming the database handle $dbh has already been connected
my $sth = $dbh->prepare('INSERT INTO(field) VALUES (?)');

# I'm skipping over error-checking here for brevity - don't skip
# it in production code!
$sth->execute($cgiobj->param('cgi-parameter'));

This allows no chance for the contents of 'cgi-parameter' to be interpreted
as part of a SQL command.

sherm--

--
Cocoa programming in Perl: http://camelbones.sourceforge.net
Hire me! My resume: http://www.dot-app.org

Thank you kindly. I believe that these two replies allay both my fears
- HTML being parsed in the result page, and SQL being injected
initially.
I appreciate the help.

"robert.waters" skrev i meddelandet
news: ups.com...
> Thank you kindly. I believe that these two replies allay both my fears
> - HTML being parsed in the result page, and SQL being injected
> initially.
> I appreciate the help.
>
Think hard do you really want them to be able to inject html tags
i.e. an unclosed H1 tag would change everything below it....

Bengt-Arne Fjellner wrote:
> "robert.waters" skrev i meddelandet
> news: ups.com...
>>
>>Thank you kindly. I believe that these two replies allay both my fears
>>- HTML being parsed in the result page, and SQL being injected
>>initially.
>>I appreciate the help.
>
> Think hard do you really want them to be able to inject html tags
> i.e. an unclosed H1 tag would change everything below it....

Not if you convert it to &lt;H1&gt;

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl

"Bengt-Arne Fjellner" <Bengt-Arne(dot)Fjellner(at)ltu(dot)se> writes:

> Think hard do you really want them to be able to inject html tags
> i.e. an unclosed H1 tag would change everything below it....

Gunnar's suggestion of using CGI::escapeHTML() addresses that - it will
replace all the '<'s in the output with &lt;, and so on.

If you want to allow a limited set of markup beyond that, you could use
"[[...]]" style "tags" similar to those used by many message boards, and
then transform only the specific set of tags you've defined into valid
HTML markup.

sherm--

--
Cocoa programming in Perl: http://camelbones.sourceforge.net
Hire me! My resume: http://www.dot-app.org

Hi,
I've decided to use HTML::Entities to do the tag encoding.
I have another question though:
How would I use DBI placeholders in the case of a multiple insert?
I've got a field that takes a comma-delimited list with no limit on
quantity.
For instance, how would I convert this example to use placeholders?:
$contentid=0;
$sql = "INSERT INTO keyword (name, contentid) VALUES (";
for (my $i=0; $i<@keywords; $i++) {

$keywords[$i] = "(qw[$keywords[$i]],$contentid)";

}
$sql .= join(',', @keywords).")"; # results in '...VALUES
(("a",0),("b",0))' etc..
$q=$dbh->prepare($sql);
$q->execute;

robert.waters wrote:

> I've decided to use HTML::Entities to do the tag encoding.
> I have another question though:

Another? You had a previous? Please quote context when posting a
reply. For more information, please see the Posting Guidelines that
are posted here twice a week.

> How would I use DBI placeholders in the case of a multiple insert?
> I've got a field that takes a comma-delimited list with no limit on
> quantity.
> For instance, how would I convert this example to use placeholders?:
> $contentid=0;
> $sql = "INSERT INTO keyword (name, contentid) VALUES (";
> for (my $i=0; $i<@keywords; $i++) {
>
> $keywords[$i] = "(qw[$keywords[$i]],$contentid)";

I don't believe for a second that that does what you think it does.
Don't re-type code. Copy and paste.

>
> }
> $sql .= join(',', @keywords).")"; # results in '...VALUES
> (("a",0),("b",0))' etc..
> $q=$dbh->prepare($sql);
> $q->execute;

I see two solutions. One messier but (possibly?) more efficient than
the other.
The cleaner one is to convert into multiple INSERT executions:
my $sql = "INSERT INTO keyword (name, contentid) VALUES (?, ?)";
my $sth = $dbh->prepare($sql);
$sth->execute($_, $contentid) for @keywords;

The messier one is to build a multiple-INSERT statement based on the
number of keywords you're going to have:
my $sql = "INSERT INTO keyword(name, contentid) VALUES (";
$sql .= join (', ', ("(?, ?)") x @keywords);
$sql .= ")";
my $sth = $dbh->prepare($sql);
$sth->execute(map { $_, $contentid } @keywords);

Like I said, this second one is definately messier. Unless there's a
signficant performance reason to use a single statement that does all
the insertions (and not being overly DBI-proficient, I have no idea if
there is), I'd recommend the first choice above.

Paul Lalli

robert.waters wrote:
> I have another question though:

Then you should have started a new thread with an appropriate subject line.

> How would I use DBI placeholders in the case of a multiple insert?
> I've got a field that takes a comma-delimited list with no limit on
> quantity.
> For instance, how would I convert this example to use placeholders?:
> $contentid=0;
> $sql = "INSERT INTO keyword (name, contentid) VALUES (";
> for (my $i=0; $i<@keywords; $i++) {
>
> $keywords[$i] = "(qw[$keywords[$i]],$contentid)";
------------------------^^
??

> }
> $sql .= join(',', @keywords).")"; # results in '...VALUES
> (("a",0),("b",0))' etc..
> $q=$dbh->prepare($sql);
> $q->execute;

Assuming you don't want the qw// operator there, I suppose you may want
to do something like:

my $name = join( ',', map "($_,$contentid)", @keywords );
$q = $dbh->prepare('INSERT INTO keyword (name,contentid) VALUES(?,?)';
$q->execute($name, $contentid);

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl