From: Eric Wong <e@80x24.org>
To: meta@public-inbox.org
Subject: [PATCH 2/2] sharedkv: avoid ambiguity for numeric-like string keys
Date: Mon, 14 Feb 2022 05:37:25 +0000 [thread overview]
Message-ID: <20220214053725.1080495-3-e@80x24.org> (raw)
In-Reply-To: <20220214053725.1080495-1-e@80x24.org>
While we only store URLs and binary SHA-1/SHA-256 values in skv
at the moment, we may store potentially ambiguous keys/values in
the future. It's possible to store "02" and have it treated as
`2' unless explicitly binding parameters as SQL_BLOB. This
behavior was independent of the sqlite_unicode parameter as
evidenced by the new tests.
I only noticed this bug while hacking on another project using
DBD::SQLite, and not while hacking on public-inbox itself.
---
lib/PublicInbox/SharedKV.pm | 30 +++++++++++++++++++++++-------
t/shared_kv.t | 3 +++
2 files changed, 26 insertions(+), 7 deletions(-)
diff --git a/lib/PublicInbox/SharedKV.pm b/lib/PublicInbox/SharedKV.pm
index d49a39c1..90ccf2b4 100644
--- a/lib/PublicInbox/SharedKV.pm
+++ b/lib/PublicInbox/SharedKV.pm
@@ -9,7 +9,7 @@ use strict;
use v5.10.1;
use parent qw(PublicInbox::Lock);
use File::Temp qw(tempdir);
-use DBI ();
+use DBI qw(:sql_types); # SQL_BLOB
use PublicInbox::Spawn;
use File::Path qw(rmtree make_path);
@@ -59,9 +59,12 @@ sub new {
sub set_maybe {
my ($self, $key, $val, $lock) = @_;
$lock //= $self->lock_for_scope_fast;
- my $e = $self->{dbh}->prepare_cached(<<'')->execute($key, $val);
+ my $sth = $self->{dbh}->prepare_cached(<<'');
INSERT OR IGNORE INTO kv (k,v) VALUES (?, ?)
+ $sth->bind_param(1, $key, SQL_BLOB);
+ $sth->bind_param(2, $val, SQL_BLOB);
+ my $e = $sth->execute;
$e == 0 ? undef : $e;
}
@@ -88,20 +91,30 @@ sub keys {
} else {
@pfx = (); # [0] may've been undef
}
- map { $_->[0] } @{$self->dbh->selectall_arrayref($sql, undef, @pfx)};
+ my $sth = $self->dbh->prepare($sql);
+ if (@pfx) {
+ $sth->bind_param(1, $pfx[0], SQL_BLOB);
+ $sth->bind_param(2, $pfx[1]);
+ }
+ $sth->execute;
+ map { $_->[0] } @{$sth->fetchall_arrayref};
}
sub set {
my ($self, $key, $val) = @_;
if (defined $val) {
- my $e = $self->{dbh}->prepare_cached(<<'')->execute($key, $val);
+ my $sth = $self->{dbh}->prepare_cached(<<'');
INSERT OR REPLACE INTO kv (k,v) VALUES (?,?)
+ $sth->bind_param(1, $key, SQL_BLOB);
+ $sth->bind_param(2, $val, SQL_BLOB);
+ my $e = $sth->execute;
$e == 0 ? undef : $e;
} else {
- $self->{dbh}->prepare_cached(<<'')->execute($key);
+ my $sth = $self->{dbh}->prepare_cached(<<'');
DELETE FROM kv WHERE k = ?
+ $sth->bind_param(1, $key, SQL_BLOB);
}
}
@@ -110,7 +123,8 @@ sub get {
my $sth = $self->{dbh}->prepare_cached(<<'', undef, 1);
SELECT v FROM kv WHERE k = ?
- $sth->execute($key);
+ $sth->bind_param(1, $key, SQL_BLOB);
+ $sth->execute;
$sth->fetchrow_array;
}
@@ -121,9 +135,11 @@ sub xchg {
if (defined $newval) {
set($self, $key, $newval);
} else {
- $self->{dbh}->prepare_cached(<<'')->execute($key);
+ my $sth = $self->{dbh}->prepare_cached(<<'');
DELETE FROM kv WHERE k = ?
+ $sth->bind_param(1, $key, SQL_BLOB);
+ $sth->execute;
}
$oldval;
}
diff --git a/t/shared_kv.t b/t/shared_kv.t
index 8b4f9c29..8dfd3b25 100644
--- a/t/shared_kv.t
+++ b/t/shared_kv.t
@@ -42,5 +42,8 @@ undef $skv;
ok(!-d $skv_tmpdir, 'temporary dir gone');
$skv = PublicInbox::SharedKV->new("$tmpdir/dir", 'base');
ok(-e "$tmpdir/dir/base.sqlite3", 'file created');
+$skv->dbh;
+ok($skv->set_maybe('02', '2'), "`02' set");
+ok($skv->set_maybe('2', '2'), "`2' set (no match on `02')");
done_testing;
prev parent reply other threads:[~2022-02-14 5:37 UTC|newest]
Thread overview: 3+ messages / expand[flat|nested] mbox.gz Atom feed top
2022-02-14 5:37 [PATCH 0/2] sharedkv: cleanup + bugfixes Eric Wong
2022-02-14 5:37 ` [PATCH 1/2] sharedkv: remove unused subs Eric Wong
2022-02-14 5:37 ` Eric Wong [this message]
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://public-inbox.org/README
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=20220214053725.1080495-3-e@80x24.org \
--to=e@80x24.org \
--cc=meta@public-inbox.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public inbox
https://80x24.org/public-inbox.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).