view release on metacpan or  search on metacpan

lib/DBIx/Raw.pm  view on Meta::CPAN

package DBIx::Raw;

use 5.008_005;
our $VERSION = '0.23';

use strictures 2;
use Moo;
use Types::Standard qw/Bool HashRef InstanceOf Str/;
use DBI;
use Config::Any;
use DBIx::Raw::Crypt;
use Carp;
use List::Util qw/first/;
use Crypt::Mode::CBC::Easy;

#have an errors file to write to
has 'dsn' => is => 'rw';
has 'user' => is => 'rw';
has 'password' => is => 'rw';
has 'conf' => is => 'rw';
has 'prev_conf' => (
    is => 'rw',
    isa => Str,
    default => '',
);

has 'crypt' => (
	is => 'ro',
	isa => InstanceOf['Crypt::Mode::CBC::Easy'],
	lazy => 1,
	builder => sub {
		my ($self) = @_;
		return Crypt::Mode::CBC::Easy->new(key => $self->crypt_key);
	},
);

has 'crypt_key' => (
    is => 'rw',
    isa => Str,
    lazy => 1,
    builder => sub {
        my $crypt_key_hex = 'aea77496999d37bf47aedff9c0d44fdf2d2bbfa848ee6652abe9891b43e0f331';
        return pack "H*", $crypt_key_hex;
    },
);

has 'use_old_crypt' => (
    is => 'rw',
    isa => Bool,
);

has 'old_crypt_key' => (
    is => 'rw',
    isa => Str,
    lazy => 1,
    default => '6883868834006296591264051568595813693328016796531185824375212916576042669669556288781800326542091901603033335703884439231366552922364658270813734165084102xfasdfa8823423sfasdfalkj!@#$$CCCFFF!09xxxxlai3847lol13234408!!@#$_+-083dxje380-=...
);

has 'old_crypt' => (
	is => 'ro',
	isa => InstanceOf['DBIx::Raw::Crypt'],
	lazy => 1,
	builder => sub {
		my ($self) = @_;
		return DBIx::Raw::Crypt->new( { secret => $self->old_crypt_key });
	},
);

# LAST STH USED
has 'sth' => is => 'rw';

#find out what DBH is specifically
has 'dbh' => (
	is => 'rw',
	lazy => 1,
	default => sub { shift->connect }
);

has 'keys' => (
	is => 'ro',
	isa => HashRef[Str],
	default => sub { {
		query => 1,
		vals => 1,
		encrypt => 1,
		decrypt => 1,
		key => 1,
		href => 1,
		table => 1,
		where => 1,
		pk => 1,
		rows => 1,
        id => 1,

lib/DBIx/Raw.pm  view on Meta::CPAN

Note that L</"ADVANCED SYNTAX"> is required for L</hoh>, L</hoaoh>, L</hash>, or L</update> because of the extra parameters that they require.

=head1 ENCRYPT AND DECRYPT

You can use L<DBIx::Raw> to encrypt values when putting them into the database and decrypt values when removing them from the database.
Note that in order to store an encrypted value in the database, you should have the field be of type C<VARCHAR(255)> or some type of character
or text field where the encryption will fit. In order to encrypt and decrypt your values, L<DBIx::Raw> requires a L</crypt_key>. It contains a default
key, but it is recommended that you change it either by having a different one in your L</conf> file, or passing it in on creation with C<new> or setting it using the
L</crypt_key> method. It is recommended that you use a module like L<Crypt::Random> to generate a secure key.
One thing to note is that both L</encrypt> and L</decrypt> require L</"ADVANCED SYNTAX">.

=head2 encrypt

In order to encrypt values, the values that you want to encrypt must be in the bind values array reference that you pass into C<vals>. Note that for the values that you want to
encrypt, you should put their index into the encrypt array that you pass in. For example:

    my $num_rows_updated = $db->raw(query => "UPDATE people SET name=?,age=?,height=? WHERE id=1", vals => ['Zoe', 24, "5'11"], encrypt => [0, 2]);

In the above example, only C<name> and C<height> will be encrypted. You can easily encrypt all values by using '*', like so:

    my $num_rows_updated = $db->raw(query => "UPDATE people SET name=?,height=? WHERE id=1", vals => ['Zoe', "5'11"], encrypt => '*');

And this will encrypt both C<name> and C<height>.

The only exception to the L</encrypt> syntax that is a little different is for L</update>. See L</"update encrypt"> for how to encrypt when using L</update>.

=head2 decrypt

When decrypting values, there are two possible different syntaxes.

=head3 DECRYPT LIST CONTEXT

If your query is returning a single value or values in a list context, then the array reference that you pass in for decrypt will contain the indices for the
order that the columns were listed in. For instance:

    my $name = $db->raw(query => "SELECT name FROM people WHERE id=1", decrypt => [0]);

    my ($name, $age) = $db->raw(query => "SELECT name, age FROM people WHERE id=1", decrypt => [0,1]);

=head3 DECRYPT HASH CONTEXT

When your query has L<DBIx::Raw> return your values in a hash context, then the columns that you want decrypted must be listed by name in the array reference:

    my $person = $db->raw(query => "SELECT name, age FROM people WHERE id=1", decrypt => ['name', 'age'])

    my $aoh = $db->aoh(query => "SELECT name, age FROM people", decrypt => ['name', 'age']);

Note that for either L</"LIST CONTEXT"> or L</"HASH CONTEXT">, it is possible to use '*' to decrypt all columns:

    my ($name, $height) = $db->raw(query => "SELECT name, height FROM people WHERE id=1", decrypt => '*');

=head2 crypt_key

L<DBIx::Raw> uses L</"crypt_key"> to encrypt and decrypt all values. You can set the crypt key when you create your
L<DBIx::Raw> object by passing it into L</new>, providing it to L<CONFIGURATION FILE|DBIx::Raw/"CONFIGURATION FILE">,
or by setting it with its setter method:

    $db->crypt_key("1234");

It is strongly recommended that you do not use the default L</"crypt_key">. The L</crypt_key> should be the appropriate length
for the L</crypt> that is set. The default L</crypt> uses L<Crypt::Mode::CBC::Easy>, which uses L<Crypt::Cipher::Twofish>, which
allows key sizes of 128/192/256 bits.

=head2 crypt

The L<Crypt::Mode::CBC::Easy> object to use for encryption. Default is the default L<Crypt::Mode::CBC::Easy> object
created with the key L</crypt_key>.

=head2 use_old_crypt

In version 0.16 L<DBIx::Raw> started using L<Crypt::Mode::CBC::Easy> instead of L<DBIx::Raw::Crypt>. Setting this to 1 uses the old encryption instead.
Make sure to set L</old_crypt_key> if you previously used L</crypt_key> for encryption.

=head2 old_crypt_key

This sets the crypt key to use if L</use_old_crypt> is set to true. Default is the previous crypt key.

=head1 SUBROUTINES/METHODS

=head2 raw

L</raw> is a very versitile subroutine, and it can be called in three contexts. L</raw> should only be used to make a query that
returns values for one record, or a query that returns no results (such as an INSERT query). If you need to have multiple
results returned, see one of the subroutines below.

=head3 SCALAR CONTEXT

L</raw> can be called in a scalar context to only return one value, or in a undef context to return no value. Below are some examples.

    #select
    my $name = $db->raw("SELECT name FROM people WHERE id=1");

    #update with number of rows updated returned
    my $num_rows_updated = $db->raw("UPDATE people SET name=? WHERE id=1", 'Frank');

    #update in undef context, nothing returned.
    $db->raw("UPDATE people SET name=? WHERE id=1", 'Frank');

    #insert
    $db->raw("INSERT INTO people (name, age) VALUES ('Jenny', 34)");

Note that to L</decrypt> for L</"SCALAR CONTEXT"> for L</raw>, you would use L</"DECRYPT LIST CONTEXT">.

=head3 LIST CONTEXT

L</raw> can also be called in a list context to return multiple columns for one row.

    my ($name, $age) = $db->raw("SELECT name, age FROM people WHERE id=1");

    #or
    my @person = $db->raw("SELECT name, age FROM people WHERE id=1");

Note that to L</decrypt> for L</"LIST CONTEXT"> for L</raw>, you would use L</"DECRYPT LIST CONTEXT">.

=head3 HASH CONTEXT

L</raw> will return a hash if you are selecting more than one column for a single record.

    my $person = $db->raw("SELECT name, age FROM people WHERE id=1");
    my $name = $person->{name};
    my $age = $person->{age};

Note that L</raw>'s L</"HASH CONTEXT"> works when using * in your query.

    my $person = $db->raw("SELECT * FROM people WHERE id=1");
    my $name = $person->{name};
    my $age = $person->{age};

Note that to L</decrypt> for L</"HASH CONTEXT"> for L</raw>, you would use L</"DECRYPT HASH CONTEXT">.
=cut