I can see how they could be if an enduser were prompted for them, but that's not how I intend to populate them.

Proverbially the road to Hell is paved with good intentions. The trouble is that it becomes harder and harder to keep track of all the possible routes through your code as it grows and the chances, however slight, of allowing external input to the variables can exist.

At the very least you should sanitise these variables as close to their point of use as possible, ie: within the subroutine. So, something like this:

sub insert_unique( $table, $column, $value ) {
    state %sths;
    if ( !exists $sths{$table} ) {
         die "Bad table '$table'" unless $table =~ /^[a-z0-9_]+$/;
         die "Bad column '$column'" unless $column =~ /^[a-z0-9_]+$/;

[download]

Better, use Carp::confess instead of die. Better still, use a hash of acceptable tables to further nail things down:

sub insert_unique( $table, $column, $value ) {
    use Carp;
    state %sths;
    state %good_table = map { $_ => 1 } qw/foo bar baz/;
    if ( !exists $sths{$table} ) {
        confess "Bad table '$table'" unless $good_table{$table};

[download]

By doing this, and the same for $column, you are limiting the ability of an external agent (or your own fat fingers) to operate on parts of the database to which there should not be access.

Oblig xkcd

🦛

In reply to Re^4: SQLite: INSERT into a unique column and retrieve rowid by hippo
in thread SQLite: INSERT into a unique column and retrieve rowid by ibm1620

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.