/ 
DBD-SQLite-1.65_02
River stage four • 518 direct dependents • 1146 total dependents
⭐ Starred 28 GitHub stars
/ DBD::SQLite::Cookbook
Security Advisories (24)
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-2019-19880 (2019-12-18)

exprListAppendList in window.c in SQLite 3.30.1 allows attackers to trigger an invalid pointer dereference because constant integer values in ORDER BY clauses of window definitions are mishandled.

CVE-2019-19244 (2019-11-25)

sqlite3Select in select.c in SQLite 3.30.1 allows a crash if a sub-select uses both DISTINCT and window functions, and also has certain ORDER BY usage.

CVE-2019-19242 (2019-11-27)

SQLite 3.30.1 mishandles pExpr->y.pTab, as demonstrated by the TK_COLUMN case in sqlite3ExprCodeTarget in expr.c.

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-20218 (2020-01-02)

selectExpander in select.c in SQLite 3.30.1 proceeds with WITH stack unwinding even after a parsing error.

CVE-2019-19924 (2019-12-24)

SQLite 3.30.1 mishandles certain parser-tree rewriting, related to expr.c, vdbeaux.c, and window.c. This is caused by incorrect sqlite3WindowRewrite() error handling.

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-19959 (2020-01-03)

ext/misc/zipfile.c in SQLite 3.30.1 mishandles certain uses of INSERT INTO in situations involving embedded '\\0' characters in filenames, leading to a memory-management error that can be detected by (for example) valgrind.

CVE-2019-19925 (2019-12-24)

zipfileUpdate in ext/misc/zipfile.c in SQLite 3.30.1 mishandles a NULL pathname during an update of a ZIP archive.

CVE-2019-19923 (2019-12-24)

flattenSubquery in select.c in SQLite 3.30.1 mishandles certain uses of SELECT DISTINCT involving a LEFT JOIN in which the right-hand side is a view. This can cause a NULL pointer dereference (or incorrect results).

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-2019-19603 (2019-12-09)

SQLite 3.30.1 mishandles certain SELECT statements with a nonexistent VIEW, leading to an application crash.

CVE-2019-19317 (2019-12-05)

lookupName in resolve.c in SQLite 3.30.1 omits bits from the colUsed bitmask in the case of a generated column, which allows attackers to cause a denial of service or possibly have unspecified other impact.

CVE-2019-19926 (2019-12-23)

multiSelect in select.c in SQLite 3.30.1 mishandles certain errors during parsing, as demonstrated by errors from sqlite3WindowRewrite() calls. NOTE: this vulnerability exists because of an incomplete fix for CVE-2019-19880.

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-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.

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.