Yeah, that's what I was assuming with regard to quoted strings. The problem wouldn't be so bad with outside quotes, but once you get into having to check whether the value was actually escape quoted ('O\'Donnel' for example) and so on... Ugh.
I suppose I can just go ahead and push the bound parameter values into an array to execute. That seemed like a clunky way to do it in my head, because it just seemed too possible to miscount or trip up... but maybe that's not as likely as I first thought.
Not to mention... quote()'ing everything is insane and I've been avoiding it as much as I can except in this specific circumstance.
Sometimes you have the answer all along and just need people to reaffirm that you're not stupid. :) Thanks. | [reply] |
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...
| [reply]
[d/l]
[select] |
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''' ?
| [reply]
[d/l]
[select] |
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...
| [reply]
[d/l] |
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. :) | [reply]
[d/l]
[select] |
