Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

$dbh->quote() is broken for SQL_BLOB type #51

Closed
pali opened this issue May 24, 2019 · 1 comment

Comments

Projects
None yet
2 participants
@pali
Copy link

commented May 24, 2019

Quoting normal string literals is working fine:

$ perl -Iblib/lib -Iblib/arch -MDBI -E 'my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:", "", "", { sqlite_unicode => 1 }); say $dbh->quote("a\"\x{27}bc");'
'a"''bc'

Literal is put into apostrophes, and every apostrophe in the middle of the literal is duplicated. Double quotes in the middle are as is. So you can put output from $dbh->quote into SQL statement without SQL injection problems.

$ perl -Iblib/lib -Iblib/arch -MDBI -E 'my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:", "", "", { sqlite_unicode => 1 }); say $dbh->selectrow_array("SELECT " . $dbh->quote("a\"\x{27}bc"));'
a"'bc

But quoting binary blobs is broken. It does not work at all. See:

$ perl -Iblib/lib -Iblib/arch -MDBI -E 'my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:", "", "", { sqlite_unicode => 1 }); say $dbh->quote("a\"\x{27}bc", SQL_BLOB);'
a"'bc

Nothing was quoted. So putting it into SQL statement would cause SQL errors or injections.

According to SQLite documentation https://www.sqlite.org/lang_expr.html:

BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. Example: X'53514C697465'

So correct quoting of SQL_BLOB should be: first do conversion into hex format and then quote it from left by X' and from right by '.

E.g. following implementation:

sub quote {
  my ($self, $value, $data_type) = @_;
  return q(X') . unpack('H*', $value) . q(') if $data_type == DBI::SQL_BLOB;
  return $self->SUPER::quote($value, $data_type);
}

charsbar added a commit that referenced this issue May 24, 2019

@charsbar

This comment has been minimized.

Copy link
Contributor

commented May 24, 2019

Applied a modified version of your patch. Thanks!

@charsbar charsbar closed this May 24, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.