/ 
DBD-SQLite-1.56
River stage four • 518 direct dependents • 1146 total dependents
⭐ Starred 28 GitHub stars
/ DBD::SQLite::Cookbook
Security Advisories (15)
CVE-2020-13434 (2020-05-24)

SQLite through 3.32.0 has an integer overflow in sqlite3_str_vappendf in printf.c.

CVE-2020-11656 (2020-04-09)

In SQLite through 3.31.1, the ALTER TABLE implementation has a use-after-free, as demonstrated by an ORDER BY clause that belongs to a compound SELECT statement.

CVE-2020-9327 (2020-02-21)

In SQLite 3.31.1, isAuxiliaryVtabOperator allows attackers to trigger a NULL pointer dereference and segmentation fault because of generated column optimizations.

CVE-2018-20506 (2019-04-03)

SQLite before 3.25.3, when the FTS3 extension is enabled, encounters an integer overflow (and resultant buffer overflow) for FTS3 queries in a "merge" operation that occurs after crafted changes to FTS3 shadow tables, allowing remote attackers to execute arbitrary code by leveraging the ability to run arbitrary SQL statements (such as in certain WebSQL use cases). This is a different vulnerability than CVE-2018-20346.

CVE-2020-13630 (2020-05-27)

ext/fts3/fts3.c in SQLite before 3.32.0 has a use-after-free in fts3EvalNextRow, related to the snippet feature.

CVE-2019-8457 (2019-05-30)

SQLite3 from 3.6.0 to and including 3.27.2 is vulnerable to heap out-of-bound read in the rtreenode() function when handling invalid rtree tables.

CVE-2020-15358 (2020-06-27)

In SQLite before 3.32.3, select.c mishandles query-flattener optimization, leading to a multiSelectOrderBy heap overflow because of misuse of transitive properties for constant propagation.

CVE-2020-13632 (2020-05-27)

ext/fts3/fts3_snippet.c in SQLite before 3.32.0 has a NULL pointer dereference via a crafted matchinfo() query.

CVE-2020-13631 (2020-05-27)

SQLite before 3.32.0 allows a virtual table to be renamed to the name of one of its shadow tables, related to alter.c and build.c.

CVE-2020-13435 (2020-05-24)

SQLite through 3.32.0 has a segmentation fault in sqlite3ExprCodeTarget in expr.c.

CVE-2020-11655 (2020-04-09)

SQLite through 3.31.1 allows attackers to cause a denial of service (segmentation fault) via a malformed window-function query because the AggInfo object's initialization is mishandled.

CVE-2019-19646 (2019-12-09)

pragma.c in SQLite through 3.30.1 mishandles NOT NULL in an integrity_check PRAGMA command in certain cases of generated columns.

CVE-2019-19645 (2019-12-09)

alter.c in SQLite through 3.30.1 allows attackers to trigger infinite recursion via certain types of self-referential views in conjunction with ALTER TABLE statements.

CVE-2018-20346 (2018-12-21)

SQLite before 3.25.3, when the FTS3 extension is enabled, encounters an integer overflow (and resultant buffer overflow) for FTS3 queries that occur after crafted changes to FTS3 shadow tables, allowing remote attackers to execute arbitrary code by leveraging the ability to run arbitrary SQL statements (such as in certain WebSQL use cases), aka Magellan.

CVE-2018-8740 (2018-03-17)

In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE TABLE AS statement could cause a NULL pointer dereference, related to build.c and prepare.c.

NAME

DBD::SQLite::Cookbook - The DBD::SQLite Cookbook

DESCRIPTION

This is the DBD::SQLite cookbook.

It is intended to provide a place to keep a variety of functions and formals for use in callback APIs in DBD::SQLite.

AGGREGATE FUNCTIONS

Variance

This is a simple aggregate function which returns a variance. It is adapted from an example implementation in pysqlite.

package variance;

sub new { bless [], shift; }

sub step {
    my ( $self, $value ) = @_;

    push @$self, $value;
}

sub finalize {
    my $self = $_[0];

    my $n = @$self;

    # Variance is NULL unless there is more than one row
    return undef unless $n || $n == 1;

    my $mu = 0;
    foreach my $v ( @$self ) {
        $mu += $v;
    }
    $mu /= $n;

    my $sigma = 0;
    foreach my $v ( @$self ) {
        $sigma += ($v - $mu)**2;
    }
    $sigma = $sigma / ($n - 1);

    return $sigma;
}

# NOTE: If you use an older DBI (< 1.608),
# use $dbh->func(..., "create_aggregate") instead.
$dbh->sqlite_create_aggregate( "variance", 1, 'variance' );

The function can then be used as:

SELECT group_name, variance(score)
FROM results
GROUP BY group_name;

Variance (Memory Efficient)

A more efficient variance function, optimized for memory usage at the expense of precision:

package variance2;

sub new { bless {sum => 0, count=>0, hash=> {} }, shift; }

sub step {
    my ( $self, $value ) = @_;
    my $hash = $self->{hash};

    # by truncating and hashing, we can comsume many more data points
    $value = int($value); # change depending on need for precision
                          # use sprintf for arbitrary fp precision
    if (exists $hash->{$value}) {
        $hash->{$value}++;
    } else {
        $hash->{$value} = 1;
    }
    $self->{sum} += $value;
    $self->{count}++;
}

sub finalize {
    my $self = $_[0];

    # Variance is NULL unless there is more than one row
    return undef unless $self->{count} > 1;

    # calculate avg
    my $mu = $self->{sum} / $self->{count};

    my $sigma = 0;
    while (my ($h, $v) = each %{$self->{hash}}) {
        $sigma += (($h - $mu)**2) * $v;
    }
    $sigma = $sigma / ($self->{count} - 1);

    return $sigma;
}

The function can then be used as:

SELECT group_name, variance2(score)
FROM results
GROUP BY group_name;

Variance (Highly Scalable)

A third variable implementation, designed for arbitrarily large data sets:

package variance3;

sub new { bless {mu=>0, count=>0, S=>0}, shift; }

sub step {
    my ( $self, $value ) = @_;
    $self->{count}++;
    my $delta = $value - $self->{mu};
    $self->{mu} += $delta/$self->{count};
    $self->{S} += $delta*($value - $self->{mu});
}

sub finalize {
    my $self = $_[0];
    return $self->{S} / ($self->{count} - 1);
}

The function can then be used as:

SELECT group_name, variance3(score)
FROM results
GROUP BY group_name;

SUPPORT

Bugs should be reported via the CPAN bug tracker at

http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBD-SQLite

TO DO

  • Add more and varied cookbook recipes, until we have enough to turn them into a separate CPAN distribution.

  • Create a series of tests scripts that validate the cookbook recipes.

AUTHOR

Adam Kennedy <adamk@cpan.org>

COPYRIGHT

Copyright 2009 - 2012 Adam Kennedy.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

The full text of the license can be found in the LICENSE file included with this module.