cgi.pm; DBI, filtering input?

[robert.waters]

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

 

[Gunnar Hjalmarsson]

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.

 

[Sherm Pendley]

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

 

[robert.waters]

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.

 

[Bengt-Arne Fjellner]

"robert.waters" skrev i meddelandet

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

 

[Gunnar Hjalmarsson]

"robert.waters" skrev i meddelandet


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 <H1>

 

[Sherm Pendley]

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

 

[robert.waters]

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;

 

[Paul Lalli]

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

 

[Gunnar Hjalmarsson]

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

 

[Sherm Pendley]

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

Won't that give a different result than what Robert's asking for?

With placeholders, the (s should be inserted as text into the name field,
not interpreted as SQL. So the result will be a single record whose name
field looks like "(a,0),(b,0),(c,0)", and contentid field is 0.

I think Robert wanted multiple records to be inserted. For that, you'd want
one prepare() followed by a series of execute()s, like this:

my $q = $dbh->prepare('INSERT INTO keyword (name,contentid) VALUES(?,?)');
foreach my $name (@keywords) {
$q->execute($name, $contentid);
}

sherm--

 

[robert.waters]

My apologies, you are absolutely right; I should have started another
thread.
Thank you for your responses.
sherm - that is what I will do; I had my mind set on one INSERT, but I
realize now that's not the best way.

 

[xhoster]

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

I don't think that that is doing what you think it is doing. The qw
is not an operator, just the letters q and w.

}
$sql .= join(',', @keywords).")";
# results in '...VALUES (("a",0),("b",0))' etc..

Even if that is what the results are, I don't think that that is correct
syntax for mysql. You shouldn't have the outer parenthesis.

Anyway, SQL text strings can be created on the fly with placeholders, just
like they can be created on the fly with data (only easier).

join ",", map "(?,?)", @keywords;

But I wouldn't bother. Just roll a loop with an execute inside it. Only
if/when that proves to be too slow would I consider trying to cram it all
into one SQL statement.

Xho