Recalcitrant placeholders

Bod has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Recalcitrant placeholders by kcott (Archbishop) on Jul 05, 2021 at 06:39 UTC
G'day Bod, I tried to reproduce your problem but couldn't. I created a test database: `$ sqlite3 bod SQLite version 3.34.0 2020-12-01 16:14:00 Enter ".help" for usage hints. sqlite> create table Person (idPerson, email, altEmail); sqlite> insert into Person values (1, 'you@example.com', ''); sqlite> insert into Person values (2, '', 'me@example.com'); sqlite> insert into Person values (3, 'me@example.com', ''); sqlite> select * from Person; 1\|you@example.com\| 2\|\|me@example.com 3\|me@example.com\| sqlite>` [download] Then a test script: #!/usr/bin/env perl use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=bod"); print "Contents of Person:\n"; eval { print join('\|', @$_), "\n" for $dbh->selectall_array('select * fro +m Person'); 1; } or do { print $dbh->errstr, "\n"; }; print "Head-scratching code:\n"; eval { my %data = (email => 'me@example.com'); my $crid = $dbh->selectrow_array("SELECT idPerson FROM Person WHER +E email = ? OR altEmail = ?", undef, $data{'email' }, $data{'email'}); print "$crid\n"; 1; } or do { print $dbh->errstr, "\n"; }; [download] Output: `Contents of Person: 1\|you@example.com\| 2\|\|me@example.com 3\|me@example.com\| Head-scratching code: 2` [download] As you can see, I had to make some guesses; however, I used a verbatim copy of your `selectrow_array()`. It correctly found the `idPerson` (i.e. `2`) using your parameters. Instead of presenting us with bits of isolated code, please provide an SSCCE that reproduces your problem and is written in such a way that we can run it and help you towards a solution. What you could also do is create a temporary directory and, in there, create the test database exactly as I have and run exactly the same test script I presented; then compare the output. — Ken	[reply] [d/l] [select]
Re^2: Recalcitrant placeholders by Bod (Parson) on Jul 05, 2021 at 20:39 UTC
Instead of presenting us with bits of isolated code, please provide an SSCCE that reproduces your problem and is written in such a way that we can run it and help you towards a solution. I cannot reproduce the problem myself... To try and get to the bottom of the problem, I have written this script: #!/usr/bin/perl -T use CGI::Carp qw(fatalsToBrowser); use FindBin qw($RealBin); my $safepath; BEGIN { if ($RealBin =~ m!^(/home/username/uk/www)!) { $safepath = "$1/../lib"; } else { die "Illegal use of software - visit www.example.com to use th +is site"; } } use lib "$safepath"; use Site::HTML; use Site::Wayfinder; use Bod::CRM; use strict; use warnings; my $crm = Bod::CRM->new('test'); print "Content-type: text/plain\n\n"; my $db = '...'; my $un = '...'; my $pw = '...'; ##### Uncomment one line ##### #my $dbh = DBI->connect("dbi:mysql:$db:localhost:3306",$un,$pw) \|\| die + "DB ERROR: " . $dbh->errstr; my $dbh = $crm->db; $dbh->do("CREATE TEMPORARY TABLE Temp_Test ( idTest INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(40), nname VARCHAR(40), sname VARCHAR(60), email VARCHAR(100), altEmail VARCHAR(100) ) ENGINE MyISAM"); print "ERROR: " . $dbh->errstr if $dbh->err; $dbh->do("INSERT INTO Temp_Test (fname, nname, sname, email) VALUES (' +Ian', 'Bod', 'Boddison', 'me\@example.com')"); $dbh->do("INSERT INTO Temp_Test (fname, nname, sname, email) VALUES (' +Boomer', 'Boo', 'Dog', 'dog\@example.com')"); $dbh->do("INSERT INTO Temp_Test (fname, nname, sname, email) VALUES (' +Oi', '', 'You', 'you\@example.com')"); my $query = $dbh->prepare("SELECT * FROM Temp_Test"); $query->execute; $, = ' - '; while (my @row = $query->fetchrow_array) { print @row; print "\n"; } my %data; $data{'email'} = 'dog@example.com'; my $crid = $dbh->selectrow_array("SELECT idTest FROM Temp_Test WHERE e +mail = ? OR altEmail = ?", undef, $data{'email'}, $data{'email'}); print "ERROR: " . $dbh->errstr if $dbh->err; print "\nCRID: $crid\n"; [download] This correctly dumps the table data and then displays `CRID: 2` So what I have done is to go back to the code that is behaving strangely and double check it. It has been stripped back to bare minimum and the strange result still happens: #!/usr/bin/perl -T use CGI::Carp qw(fatalsToBrowser); use FindBin qw($RealBin); my $safepath; BEGIN { if ($RealBin =~ m!^(/home/username/uk/www)!) { $safepath = "$1/../lib"; } else { die "Illegal use of software - visit www.example.com to use th +is site"; } } use lib "$safepath"; use Site::HTML; use Site::Wayfinder; use Bod::CRM; use strict; use warnings; my $html = Site::HTML->new; my $wf = Site::Wayfinder->new; my $crm = Bod::CRM->new('test'); $html->head; my $logbox = 'log_login'; $logbox = 'log_fpass' if $data{'command'} eq 'fpass'; # Reset password if ($data{'command'} eq 'rpass') { my $test = $crm->db->selectrow_array("SELECT idPerson FROM Person +WHERE email = ? OR altEmail = ?", undef, $data{'email'}, $data{'email +'}); print "<p>TEST: $test</p>\n"; my $query = $crm->db->prepare("SELECT idPerson FROM Person WHERE e +mail = ? OR altEmail = ?"); $query->execute($data{'email'}, $data{'email'}); my $crid = $query->fetchrow_array; print "<p>CRID: $crid</p>\n"; exit; } __END__ [download] This has two ways of getting the same data from the database. The output is: `TEST: 0 CRID: 1` [download] The `selectrow_array` function should not be able to return zero when zero does not exist anywhere in the `idPerson` field of the database. update: Just to prove it is not something that is happening in `Site::HTML->head`, I have changed: `$html->head;` [download] for `#$html->head; print "Content-type: text/plain\n\n";` [download] and the output now is: `<p>TEST: 0</p> <p>CRID: 1</p>` [download]	[reply] [d/l] [select]
Re^3: Recalcitrant placeholders by kcott (Archbishop) on Jul 07, 2021 at 07:04 UTC
"I cannot reproduce the problem myself..." Why not? Please explain. "To try and get to the bottom of the problem, I have written this script:" But why post it here. It's littered with all sorts of things unrelated to the base problem which, until we know more, would seem to be database-related. There is taint-related code, web-related code, and three non-CPAN modules about which we basically know nothing. This is not an SSCCE. I wrote a succinct test and suggested you try it: that doesn't appear to have happened. At the time of writing, you hadn't told us what database you were using. I chose SQLite because it's common and easy to use. If you don't have it, you should be able to write something equally simple with `"dbi:SQLite:..."` replaced with `"dbi:mysql:..."`. "The `selectrow_array` function should not be able to return zero when zero does not exist anywhere in the `idPerson` field of the database." What's the basis for that claim? In "DBI: selectrow_array", there are three examples with '`@row_ary = ...`'; none with '`$some_scalar = ...`'. It talks about how calling this in a scalar context can be handled differently depending on the driver being used. It ends with "... you should exercise some caution if you use `selectrow_array` in a scalar context, or just don't do that." [my emphasis] Perhaps what you're seeing is something akin to: `$ perl -E 'my @x = (); my $y = @x; say $y' 0` [download] If, after writing an SSCCE and perhaps resolving issues, this still fails in your main application, I would suggest the next step would be to write a `/path/to/Bod/CRM/NN-selectrow_array.t` and compare '`$dbh->selectrow_array(...)`' with '`$crm->db->selectrow_array(...)`'. My code shown so far uses Perl(5.34.0), DBI(1.643) and DBD::SQLite(1.66). I don't have DBD::mysql installed and I haven't used MySQL for about 20 years, so I can't really help you with that. — Ken	[reply] [d/l] [select]
Re^4: Recalcitrant placeholders by Bod (Parson) on Jul 09, 2021 at 18:03 UTC
Re^5: Recalcitrant placeholders by haukex (Archbishop) on Jul 09, 2021 at 19:51 UTC
Some notes below your chosen depth have not been shown here
Re^5: Recalcitrant placeholders by hippo (Bishop) on Jul 09, 2021 at 19:54 UTC
Re: Recalcitrant placeholders by GrandFather (Saint) on Jul 04, 2021 at 21:22 UTC
Of error handling and calling selectrow_array in scalar context the DBI documentation says: If any method fails, and "RaiseError" is not set, selectrow_array will return an empty list. If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an undef is returned if there are no more rows or if an error occurred. That undef can't be distinguished from an undef returned because the first field value was NULL. For these reasons you should exercise some caution if you use selectrow_array in a scalar context, or just don't do that. Does any of that ring alarm bells? Maybe setting RaiseError will help if you haven't already? Optimising for fewest key strokes only makes sense transmitting to Pluto or beyond	[reply]
Re^2: Recalcitrant placeholders by Bod (Parson) on Jul 04, 2021 at 22:18 UTC
The query doesn't return more than one column. However, I checked it wasn't a scalar issue in one of the things I tried... - Forcing array context my ($crid) = $crm->db->selectrow_array - no change Raise Error is on by default and would be caught by the error trapping on the next line. edit - it also isn't returning `undef`, it is returning zero which is what is really confusing.	[reply] [d/l]
Re: Recalcitrant placeholders by hippo (Bishop) on Jul 04, 2021 at 21:21 UTC
`SELECT idPerson FROM Person WHERE email = 'me@examle.com' OR altEmail = 'me@example.com'` ... Hardcoding the email `WHERE email = 'me\@example.com'` - that works! Those email addresses are not the same. 🦛	[reply] [d/l] [select]
Re^2: Recalcitrant placeholders by Bod (Parson) on Jul 04, 2021 at 22:20 UTC
WHERE email = 'me@examle.com' OR altEmail = 'me@example.com' That fails at compilation time saying that `@example is not declared`	[reply] [d/l]
Re^3: Recalcitrant placeholders by LanX (Saint) on Jul 04, 2021 at 22:42 UTC
Not what hippo said. Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery}	[reply]
Re^3: Recalcitrant placeholders by GrandFather (Saint) on Jul 04, 2021 at 23:39 UTC
~~A copy and paste of "examle" is the correct thing to do, but it's not the same as copy and comprehend: `"examle" ne "example"`.~~ Update: ENOTENOUGHCOFFEE Optimising for fewest key strokes only makes sense transmitting to Pluto or beyond	[reply] [d/l]
Re: Recalcitrant placeholders by Fletch (Bishop) on Jul 04, 2021 at 21:48 UTC
General tip: enabling `trace` on your handles can be elucidating to see how things are actually expanding and what's being passed into the DB engine. Before your execute do `$query->trace(2)` (or some variation). The cake is a lie. The cake is a lie. The cake is a lie.	[reply] [d/l] [select]
Re^2: Recalcitrant placeholders by Bod (Parson) on Jul 04, 2021 at 22:23 UTC
Thanks Fletch I shall try that tomorrow and it might help explain what is going on.	[reply]
Re: Recalcitrant placeholders by shmem (Chancellor) on Jul 05, 2021 at 19:00 UTC
What happens if you do this? `my $crid = $crm->db->selectrow_array("SELECT idPerson FROM Person WHER +E email = ? OR altEmail = ?", undef, quotemeta $data{'email'}, quotem +eta $data{'email'});` [download] perl -le'print map{pack c,($-++?1:13)+ord}split//,ESEL'	[reply] [d/l]
Re^2: Recalcitrant placeholders by afoken (Chancellor) on Jul 06, 2021 at 20:43 UTC
`my $crid = $crm->db->selectrow_array("SELECT idPerson FROM Person WHER +E email = ? OR altEmail = ?", undef, quotemeta $data{'email'}, quotem +eta $data{'email'});` [download] That looks just wrong. There should be absolutely no need to quote bind values in DBI. Adding any kind of extra quoting just makes things worse. DBI and the DBD take care of everything needed to pass bind parameters to the database. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l]
Re^2: Recalcitrant placeholders by Bod (Parson) on Jul 05, 2021 at 20:47 UTC
`$crid` is `undef`	[reply] [d/l] [select]
Re^3: Recalcitrant placeholders by afoken (Chancellor) on Jul 06, 2021 at 21:21 UTC
`my $crid = $crm->db->selectrow_array` `$crid` is `undef` Why am I not surprised? Let's RTFM: selectrow_array `@row_ary = $dbh->selectrow_array($statement); @row_ary = $dbh->selectrow_array($statement, \%attr); @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);` [download] This utility method combines "`prepare`", "`execute`" and "`fetchrow_array`" into a single call. If called in a list context, it returns the first row of data from the statement. The `$statement` parameter can be a previously prepared statement handle, in which case the prepare is skipped. If any method fails, and "`RaiseError`" is not set, `selectrow_array` will return an empty list. If called in a scalar context for a statement handle that has more than one column, it is undefined whether the driver will return the value of the first column or the last. So don't do that. Also, in a scalar context, an `undef` is returned if there are no more rows or if an error occurred. That undef can't be distinguished from an undef returned because the first field value was NULL. For these reasons you should exercise some caution if you use `selectrow_array` in a scalar context, or just don't do that. Let me shorten that a little bit for weary eyes: selectrow_array() is designed to be called in LIST context selectrow_array() returns an empty LIST on error Behaviour in scalar context is not well-defined In scalar context, you can't tell the difference between an error and NULL returned DBI warns not just once, but twice twice to AVOID SCALAR CONTEXT for call selectrow_array() Now, error handling. DBI offers a tedious, error-prone and highly annoying way of error handling, and a smart and lazy way of error handling. The tedious way is to add an extra error check to almost any DBI method call. Just like you would have to do in assembler or in C. You need to study the DBI documentation to find out how each method behaves on error. And yes, they do behave differently. Or, you could have DBI and perl do all of that grunt work for you, by just adding `RaiseError => 1` to the attribute hash of connect(). That automatically adds error checks to all methods, and all methods will handle errors in the same way: They die instead of returning special error indicator values. And that's almost always exactly what you want. It's also extremely useful for doing transactions, see "Transactions" in the DBI documentation. This is copied right out of that section: `use Try::Tiny; $dbh->{AutoCommit} = 0; # enable transactions, if possible $dbh->{RaiseError} = 1; try { foo(...) # do lots of work here bar(...) # including inserts baz(...) # and updates $dbh->commit; # commit the changes if we get this far } catch { warn "Transaction aborted because $_"; # Try::Tiny copies $@ into +$_ # now rollback to undo the incomplete changes # but do it in an eval{} as it may also fail eval { $dbh->rollback }; # add other application on-error-clean-up code here };` [download] (You could also do without Try::Tiny, by using an eval BLOCK that returns a true value:) `$dbh->{AutoCommit} = 0; # enable transactions, if possible $dbh->{RaiseError} = 1; eval { foo(...) # do lots of work here bar(...) # including inserts baz(...) # and updates $dbh->commit; # commit the changes if we get this far 1; } or do { warn "Transaction aborted because $@"; # now rollback to undo the incomplete changes # but do it in an eval{} as it may also fail eval { $dbh->rollback }; # add other application on-error-clean-up code here };` [download] There are rare cases when you expect an SQL statement to fail (like testing if a table exists by simply using it in an SQL statement). Wrap them into eval BLOCK or try/catch and you are done. And finally, doing database stuff in a web context with `RaiseError` set. Yes, an uncaught (i.e. unhandled) database error will kill your program, resulting in an ugly "500 Internal Server Error" or something similar. That's a good thing. It tells you that your code still has errors. As I wrote: If you expect database errors, wrap them in eval BLOCK or try/catch. For a simple-minded CGI, wrapping all of the database stuff in a big eval BLOCK and returning a "database oopsie" page on error is often acceptable. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]
Re: Recalcitrant placeholders by stevieb (Canon) on Jul 04, 2021 at 20:39 UTC
Instead of `WHERE email = 'me\@example.com'`, what happens if you use `WHERE email = 'me@example.com'`? Does it break the same way?	[reply] [d/l] [select]
Re^2: Recalcitrant placeholders by Bod (Parson) on Jul 04, 2021 at 22:11 UTC
Does it break the same way? Yes - it complains that I have not defined `@example`!	[reply] [d/l]
Re^3: Recalcitrant placeholders by choroba (Cardinal) on Jul 05, 2021 at 22:40 UTC
Who complains? Perl or the database? `map{substr$_->[0],$_->[1]\|\|0,1}[\\|\|{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^ARGV,3]`	[reply] [d/l]
Re^4: Recalcitrant placeholders by Bod (Parson) on Jul 05, 2021 at 22:53 UTC
Re^5: Recalcitrant placeholders by afoken (Chancellor) on Jul 06, 2021 at 21:39 UTC
Re: Recalcitrant placeholders by Anonymous Monk on Jul 05, 2021 at 03:00 UTC
Which database driver exactly? Tip: Use Data::Dump for debugging not plain print	[reply]


more useful options
	PerlMonks