Re: Re: Test if string is already quote()'d?

[demerphq]

The good thing is that this isnt a general case. He isn't trying to extract a quoted string from a stream afaict, but rather from a variable that he probably has considerable control over. In that case I would imagine that something like

my $arg=shift;
$arg=~s/\A(["'])(.*)\1\z/$2/s;
$arg=quote($arg);

would be perfectly acceptable, and if he had total control over the input then a simple $arg=quote($arg) unless ($arg=~/\A(["']).*\1\z/s) would suffice, and might even offer interesting power play opportunities.

---
demerphq

_{<Elian> And I do take a kind of perverse pleasure in having an OO assembly language...}

[mobiGeek]

If this is the case, then I would suggest enforcing a coding standard along the lines of never quote a string until just before applying to the database.

The question is a bit vague. Where are these strings coming from (database? user? other code?)? Maybe the O.P. should ask themself: what is the contract of the code that I am writing?.

It sounds like the current contract is something like: put stuff in the database that may or may not be ready to go in the database. Since there is no way to tell if something is ready or not, the contract cannot be fulfilled.

...And you can't tell if something is prepared or not. If I hand you the string 'hello', do I want you to insert 'hello' or do I want you to insert '''hello''' ?

[demerphq]

It sounds like the current contract is something like: put stuff in the database that may or may not be ready to go in the database. Since there is no way to tell if something is ready or not, the contract cannot be fulfilled.

I could just as easily interpret the OP's contract as being "put stuff in a DB, quoting the things that on face value arent already quoted and ignoring things that on face value are quoted. Ensuring that something that looks quoted actually is the callers problem."


---
demerphq

_{<Elian> And I do take a kind of perverse pleasure in having an OO assembly language...}

[Seumas]

It's true that I do have a degree of control over things here in that all of the software code is written by my hand and my hand only - and that the input comes almost entirely from form-input by users.

Part of my intention here, however, was to decide whether I would have to force myself to always send quoted or non-quoted variables to my functions and stick with that practice, or if I could very easily write my functions so that they could say "oh, this is already quoted so I'll use it as-is" and "this one isn't quoted, so I'll quote() first before applying it into the SQL query".

Also, doing some quote testing would probably suck as I allow HTML in the input (it's an auction site).

Since I was finally able to refine my abstraction layer to something very simple when it comes to the INSERT and UPDATES, I've decided to stick with the practice of _NEVER_ quote()'ing anything. I'll just pass everything to my Auction::DB class and let it do the work (I know, I should have done this in the first place, but I was looking for a solution outside of putting it in my DB class since I wasn't quite ready to roll it out yet).


Here is the solution I have used:

I have an sqlInsert() method in my Auction::DB class:



sub sqlInsert {
  my($self, $table, $data) = @_;
  my $names = join(", ", map { /^[_|-](.*)/; } keys %{$data});
  my $pholders = join(', ', ('?') x keys %{$data});
  $self->_sqlExecute("INSERT INTO $table ($names) VALUES($pholders)", 
+values %{$data});
}

sub _sqlExecute {
  my ($self, $sql, @values) = @_;
$self->_sqlConnect();
  my $retval;
    eval {
      my $sth = $self->{_dbh}->prepare($sql);
      $retval = $sth->execute(@values);
    };
    if ($@) {croak ($@ . "\nSQL: $sql\n" . Dumper(\@_));}
    return $retval;
}



Then I call it like this (by the way, I use PostgreSQL but this should work with most DBs):



sub createNewAccount {

  my ($self, $fields) = @_;
    # Verify required fields.
    $self->_validateUsername($fields->{_USERNAME});
    $self->_validateEmailAddress($fields->{_EMAIL_ADDRESS});

    # Populate other required fields.
    $fields->{_PASSWORD} = $self->_makePassword();
    $fields->{_CREATION_DATE} = 'now()';
  }

  $self->sqlInsert('user_accounts',$fields);    
  $self->sqlCommit;
}



Now, I never need to concern myself with quoting again. And yes, I know I should have just done this in the first place, but I was looking to tidy up my existing code separately from my transition to a mod_perl/OO based re-write of the site (which I am actually in the process of doing right now).

I also understand that Perl is a horrible first language and it's a really bad idea to make Perl your first introduction to object oriented coding. However, I started my software in Perl five years ago, that's what it's in now, it's what I know the best (so far) and it's more important to maintain/improve the software and the site as it is now than to rewrite a 16,000+ line program in an entirely different (and new to me) language.

Fortunately, I've had Perlmonks to refer to as a lot of the pitfalls and questions I've encountered during this transition to OO have already been asked by others. :)