paragonie/easydb

Easy-to-use PDO wrapper for PHP projects.

easydb's Issues

Odd issue with 1D Array errors

So if I run this query...

$db->run('SELECT * FROM users WHERE userid=? AND first_name=?', [1, 'Kyle'])

I get this message. As this is clear to me that I'm only passing a 1D array, but I'm not sure why this error keeps popping out.

Fatal error: Uncaught InvalidArgumentException: Only one-dimensional arrays are allowed.
File: EasyDB.php:651

Any ideas?

Using LOAD DATA LOCAL INFILE with easydb

So, I have a text file with over 6 million lines of data, I have no options to use insert() as this leaks my server PHP memory as this takes almost 15 mins to import 6 millions rows. So I had to use the run() function to import the text file with load infile sql function but it tells me this...

General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll() or setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY

Which I thought the run() function executes fetchAll() which it does, so I tried setting the buffer query with setAttribute() and it didn't work, same error, which left me another option to try...

PDO::ATTR_EMULATE_PREPARES => true

Which I used to setAttribute(), and it tells me it doesn't allow the use of emulated prepared statements, which would be a security downgrade.

How can I use LOAD DATA LOCAL INFILE with easydb?

Thanks!

automated tests broken on hhvm

e84ed96 reverts deb3a69 which stops tests from running on hhvm.

tests?

Cannot see any obvious unit testing, how reliable is this?

select in(...)

What protections does this offer for SELECT blah FROM blah WHERE blah IN($injected)

type queries

running automated tests on windows

So it seems that running tests on windows is always a pain.

SignpostMarv/easydb@018da94

Trying to find a nicer way of doing that without breaking it on linux. So SignpostMarv/easydb@354d816 builds on travis, but personally I'd be voting in favour of changing travis back to running phpunit.sh directly over the aesthetics of composer install; composer run tests;, e.g. SignpostMarv/easydb@bd6d74b

How to run trigger queries

how can you run trigger queries. I get the following error.
Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.
I have tried with and without using delimiter statements and it still did not work.

   $triggerquery = " CREATE TRIGGER `$triggername` $triggertime ON `$tablename` 
							FOR EACH ROW 
							BEGIN 
								IF(NEW.`$triggercol` IS NULL) 
								THEN 
									SET NEW.`$triggercol` = UNIX_TIMESTAMP(); 
								END IF; 
							END;
						";

Multiple IN's

Would like to be able to use multiple IN's in a WHERE statement without errors about only one dimensional arrays being allowed.

$states[] = (IL, IN, VA);
run("SELECT id FROM states WHERE state IN (?)", $states);

The only way to achieve this is to do the following, which leaves one open to injection attacks, right?

run("SELECT id FROM states WHERE state IN (" . implode(',', $states) . ")");

Difficult to use Postgres RETURNING clause

Current it is not possible to use insert() or insertGet() to return the last inserted id. For instance, with Postgres we can return the inserted PK but since the insert() method forces the return value be a single (int), a custom insert has to be executed instead.

Ideally, it would be possible to do something like this:

$result = $db->insertReturning($table, $values, $column);

// as per the PHP docs example:
// INSERT INTO employee(user_id, name) VALUES(?, ?) RETURNING employee_id

$values = ['user_id' => 5, 'name' => 'John Smith'];
$id = $db->insertReturning('employee', $values, 'employee_id');

Alternatively, this can be abstracted as insertGetId() since some databases (MySQL, for example) will return the correct thing from PDO::lastInsertId().

rfc, flat transaction method ?

A pattern I'm using a lot at work is something like this:

$db = $cms->getDatabaseConnection();
$autoStartTransaction = ($db->inTransaction() === false);
if ($autoStartTransaction) {
    $db->beginTransaction();
}
try {
/**
Do thing here
*/
    if ($autoStartTransaction) {
        $db->commit();
    }
} catch (Throwable $e) {
    if ($autoStartTransaction) {
        $db->rollBack();
    }
    throw $e;
}

I'm wondering if there'd be a desirable utility in having a method with a signature something like this:

public function TryFlatTransaction(callable $cb)
{
    $autoStartTransaction = ($db->inTransaction() === false);
    if ($autoStartTransaction) {
        $db->beginTransaction();
    }
    try {
        $cb($this);
        if ($autoStartTransaction) {
            $db->commit();
        }
    } catch (Throwable $e) {
        if ($autoStartTransaction) {
            $db->rollBack();
        }
        throw $e;
    }
}

Where usage is something like this:

class Foo
{
    private $db;
    function __construct(EasyDB $db) {
        $this->db = $db;
    }

    function doThing($input) {
        $this->db->TryFlatTransaction(function (EasyDB $db) use ($input) {
            $db->safeQuery(/* some query */, /* some args from $input*/); // might throw an exception
            /**
            do something else that might throw an exception
            */
        });
    }

    function doRecursiveThing($input) {
        foreach ($input as $maybeRecursive) {
            $this->db->TryFlatTransaction(function (EasyDB $db) use ($maybeRecursive) {
                $this->doThing($maybeRecursive);
                if (
                    // some recursive check
                ) {
                    $this->doRecursiveThing($maybeRecursive);
                }
            });
        }
    }
}

Removed phpcs from Travis CI checks

2fde3d8

It erroneously believes that there is no code coverage for Factory:;create() despite an explicit unit test being created for Factory::create().

I don't care to dive into the idiosyncrasies of this library. I've removed it from the Travis CI checks. It's wasting my time with a false build failure.

How is the right way with row function and multi parameters ?

i try:
$db->row("SELECT * FROM customers WHERE email = ? AND actkey = ?",array($email,$actkey));
but it don't work. Error comes up : Only one-dimensional arrays are allowed,
what did i wrong ?

But can easyDB be more sugary

...like meekrodb?

Error Management

if i try to get a row that doesnt exist, follow error will show on page:
Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'order clause' in /var/www/

how can i catch that error so that the visitor can't see this error, and i can show own error message and log this easydb error to my own logfile ?

is this a redundant is1DArray() check?

Haven't had time to write tests at work, started fleshing out the tests today & noticed something- not 100% sure, but is the second EasyDB::is1DArray() check in EasyDB::insertMany() redundant?

• First Check
• Second Check

Version 1.1.0 contains PHP 7 hints

The current 1.1.0 tag is not compatible with PHP 5.6. It looks identical to tag 2.1.0:

1.1.0...2.1.0

INSERT statements fail with boolean arguments on PostgreSQL

Uncaught PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type boolean: ""

We need a variant of buildInsertQuery() that doesn't puke on boolean arguments.

Checking a query executed succesfully

how can you verify if a query was executed successfully. is there any bool returned?

query re: paragonie/corner

If one were to tinker with the exceptions thrown by easydb, would it be better to change ParagonIE\EasyDB\Exception\ExceptionInterface to extend from ParagonIE\Corner\CornerInterface, or extend the exception classes & implement CornerInterface there ?

UTF8 vs utf8mb4

is there any greater vulnerability to using utf8mb4 than utf8? Also will unicode automatically map to utf8mb4 for mysql / percona / mariadb?

How well does this work with other DBMS?

EasyStatement uses incorrect precedence when nested

$sth1 = \ParagonIE\EasyDB\EasyStatement::open();
$sth1->with("a=1");
$sth1->orWith("a=2");
$sth1->orWith("a=3");

$sth2 = \ParagonIE\EasyDB\EasyStatement::open();
$sth2->with("status=1");
$sth2->andWith($sth1);

print $sth2;
// -> status=1 AND a=1 OR a=2 OR a=3

In MySQL, AND has a higher precedence than OR, so this clause is parsed as

(status=1 AND a=1) OR (a=2) OR (a=3)

Passing an EasyStatement as an argument to another statement should wrap it in parentheses.

Version 2.0.0

I should be releasing version 2 before tomorrow. If anyone would care to give me feedback on the changes I've made this evening, please do.

Proposal to remove the redefining of PDOExceptions

I had an issue today that was being masked by an incorrect error message. Had the library have returned a PDOException from the Factory class I would have been able to solve the issue much quicker.

Can I propose the remove of the redefining of PDOException's? I see no purpose in them as the PDOExceptions are already clear and readable.

At the very least return $e->getMessage() because the following catchall is wrong:

 'Could not create a PDO connection. Please check your username and password.'

You could completely remove that try catch block and allow the user to try/catch PDOException because at present it's just rethrowing?

Referring to this block: https://github.com/paragonie/easydb/blob/master/src/Factory.php#L86

Why not use PDO::ATTR_DRIVER_NAME?

Currently in order to enable the correct quoting string, we need to pass $dbEngine. It looks like this code could be replaced with:

if (empty($dbEngine)) {
  $dbEngine = $db->getAttribute(PDO::ATTR_DRIVER_NAME);
}

Any reason not to do this?

Fetch mode is always FETCH_ASSOC

Given the following setup:

$options = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
];
$db = \ParagonIE\EasyDB\Factory::create(
    $dsn,
    $username,
    $password
    $options
);

...we should respect the configured setting in a backwards-compatible way. (i.e. if explicitly passed, use that, otherwise, default to FETCH_ASSOC like we always do).

Allow more characters in field names

I tried to use EasyDB with an in-memory sqlite DB with special field names in the table. EasyDB doesn't accept them. I changed allowSeperators to true but than it filters characters from my fieldlist and doesn't find it anymore. SQlite itself doesn't have problems with this.

To reproduce, try this:

$db = \ParagonIE\EasyDB\Factory::create('sqlite::memory:');
php > $db->setAllowSeparators(true);
php > $db->run('create table test(`a/b` TEXT);'); // works
php > $db->insert('test', [ 'a/b' => "test" ]); // exception thrown

Causes:

Warning: Uncaught PDOException: SQLSTATE[HY000]: General error: 1 table test has no column named ab in /usr/src/app/vendor/paragonie/easydb/src/EasyDB.php:641
Stack trace:
#0 /usr/src/app/vendor/paragonie/easydb/src/EasyDB.php(641): PDO->prepare('INSERT INTO "te...')
#1 /usr/src/app/vendor/paragonie/easydb/src/EasyDB.php(434): ParagonIE\EasyDB\EasyDB->safeQuery('INSERT INTO "te...', Array, 4, true)
#2 php shell code(1): ParagonIE\EasyDB\EasyDB->insert('test', Array)
#3 {main}
  thrown in /usr/src/app/vendor/paragonie/easydb/src/EasyDB.php on line 641

Version 2.4.0

I'm going to give EasyDB a very quick code review. Once this is closed, consider the review completed.

Once this is done, at your earliest convenience, @shadowhand, can you convert and create the PHP 5.x release? I'll tag/sign/release 2.4.0 shortly thereafter.

Informix not escape column and table name

I'm working with an Informix DB engine and I discovered that, in the CRUD operations, Informix generates an error if the colums and table names are escaped. I have updated the function escapeIdentifier(...) as shown below:

public function escapeIdentifier(string $string, bool $quote = true): string
{
....
 if ($quote) {
            switch ($this->dbEngine) {
                case 'mssql':
                    return '[' . $str . ']';
                case 'mysql':
                    return '`' . $str . '`';
                case 'informix':
                    return $str;                   
                default:
                    return '"' . $str . '"';
            }
        }
        return $str;
}

The solution it is under test and it seems work perfectly!

Best regards
Francesco

How to run CREATE TABLE queries? (run and query methods fail)

I have the following setup:

$db = Factory::create('sqlite:memory.db');
$db->run($sql);
// respectively
$db->query($sql);

The following CREATE TABLE query:

CREATE TABLE IF NOT EXISTS predicate (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    value   TEXT,
    UNIQUE(value)
);

fails to run using run and query method.

run method

Error:

TypeError: Argument 1 passed to ParagonIE\EasyDB\EasyDB::getResultsStrictTyped() 
must be an instance of PDOStatement, boolean given, called in 
.../vendor/paragonie/easydb/src/EasyDB.php on line 759

.../vendor/paragonie/easydb/src/EasyDB.php:1042
...vendor/paragonie/easydb/src/EasyDB.php:759
.../vendor/paragonie/easydb/src/EasyDB.php:722
.../Adapter/SQliteMemory/Store.php:81
.../Adapter/SQliteMemory/StoreTest.php:14

query method

TypeError: Return value of ParagonIE\EasyDB\EasyDB::query() must be an instance of 
PDOStatement, boolean returned

.../vendor/paragonie/easydb/src/EasyDB.php:1197
.../src/beardfulldb/Adapter/SQliteMemory/Store.php:81

What did i wrong?

503 Service Unavailable

When I try to connect to the database through EasyDB I get an error 503 Unavailable service.
Through PDO everything is fine.

require_once "vendor/autoload.php";
$DB = \ParagonIE\EasyDB\Factory::create(
    'mysql:host=localhost;dbname=testing',
    'testing',
    'HN8yu1s1'
);

PHP7.1 cgi

Connection Issue

First of all, I really like how easydb is coded, it's easy and brief than using the actually PDO stuff!

I downloaded easydb with composer, using paragonie/easydb v2.2.1.

I'm just having a weird odd issue right now with easydb connecting, I'm not sure where the issue can be, I checked the SQL server, it appears to be online and running. Using MariaDB 5.5.52.

This is what I used to connect via easydb...

$db = \ParagonIE\EasyDB\Factory::create( 'mysql;host=localhost;dbname=databasename', 'databaseuser', 'databasepw' );

And this is the error it outputted...

Fatal error: Uncaught ParagonIE\EasyDB\Exception\ConstructorFailed: Could not create a PDO connection. Please check your username and password. in /var/www/vhosts/phoenix/httpdocs/area52/system_int/composer/vendor/paragonie/easydb/src/Factory.php:56 Stack trace: #0 /var/www/vhosts/phoenix/httpdocs/area52/system_int/bootstrap.inc.php(67): ParagonIE\EasyDB\Factory::create('mysql;host=loca...', 'databaseuser', 'databasepw') #1 /var/www/vhosts/phoenix/httpdocs/area52/index.php(3): require('/var/www/vhosts...') #2 {main} thrown in /var/www/vhosts/phoenix/httpdocs/area52/system_int/composer/vendor/paragonie/easydb/src/Factory.php on line 56

So I thought it was a error in my credentials, I checked and they were correct, however this wasn't the case after I used straight PDO from PHP and it worked by using this...

$db = new PDO('mysql:host=localhost;dbname=databasename;charset=utf8', 'databaseuser', 'databasepw');

I checked the easydb source code, and I can't figure out what can be the issue. I'm hoping you can help me out here? My PHP version is 7.1.

Thanks!

Multiple issues with EasyDB::insertGet

The documentation for this function doesn't clearly explain its purpose. As best I can tell, it's intended to insert a row, then return its auto-increment ID. (Is this accurate?)

The straightforward way of doing this would be to use PDO::lastInsertId(), which is already exposed as EasyDB::lastInsertId(). However, insertGet() instead takes the roundabout approach of performing a follow-up SELECT using every column in the inserted data as a condition. This is likely to result in strange query plans, especially in tables with many indices. Under some contrived circumstances (e.g, a table with an index covering a low-cardinality column and a monotonically decreasing column), the resulting plan may end up scanning a large portion of the table before finding the intended row.

If insertGet() is called with a $map which matches data which already exists in the table and $field is not the auto-increment column, the value returned by insertGet() may correspond to a different row than the one which was most recently inserted. (Under the mysql and pgsql drivers, it will be the largest value which exists in a matching row; under other drivers, it will be an arbitrary value.)

If $map is empty, the SELECT will fail entirely, as the WHERE clause is empty.

Using MySQL functions in insert() or update()

Do you have thoughts on how this wrapper could be modified to safely allow MySQL functions as parameters when using insert() or update()? These two shortcuts are tremendously useful but not being able to use them with native MySQL functions restricts their use in some areas.

As an example, we have queries that make use of NOW() which would not use any parameter marker. We also have queries that make use of NET_ATON(ip) which would still use a parameter marker ?

EasyStatement: Support Joins

Before I release v2.1.0 and v1.1.0 I intend to add support for JOINs:

• JOIN
• LEFT JOIN

I'm tackling this tonight.

Suggestion, return an named array

This seems like a great library!

What I feel could easily be added would be a function that delivers an ready array, Something to replace

$rows = $easydb->run();
foreach ($rows as $row) {
    $arr[$row[$keyfield]] = $row[$valuefield];
}

As a second suggestion, an array with a row key and all the other fields as rows in the array, that would replace

$rows = $easydb->run();
foreach ($rows as $row) {
    $arr[$row[$keyfield]] = $row;
}

Impossible to use update method with EasyStatement

Can you add an opportunity to use EasyStatement with the update method?
For example:

$db->update(
    'database_table',
    ['event_status' => self::EVENT_STATUS_PROCESSING],
    EasyStatement::open()->in('event_id IN (?*)', $ids)
);

Allow more characters in field names (once more, with feeling)

Vaguely related to #48, mysql allows fields starting with numbers. it also supports the full unicode BMP except U+0000, although that's not a pressing issue for me at present.

• mysql docs: https://dev.mysql.com/doc/refman/8.0/en/identifiers.html
• reproduction: $db->escapdeIdentifier('50pence');

Composer version Installation

When I use composer require paragonie/easydb:^2, it installs 2.0.0.
2.0.0 does not allow me to connect to my database. But, after forcing 2.8.0, with the same code I am able to connect to my database.

php 5 support

Is there any legacy support for PHP 5 with easydb? I noticed right away there were some PHP 7 features in the source code. I need a good PDO wrapper for an older project.

How to run a statement/query without parameters?

I have this query:
SELECT MAX(fieldName) AS maxValue FROM myTable;

I'll try to fetch the single column:
$maxValue = $db->cell( "SELECT MAX(fieldName) AS maxValue FROM myTable;",null);

The public function single(... returns the error HY093: Invalid parameter number: parameter was not defined.

How to run a PDO:statement without parameters?
Thanks in advance, Francesco

Thows 'Only one-dimensional arrays are allowed' usin 1D array

$rows = $db->run('SELECT ?', 1); // works
$rows = $db->run('SELECT ?', [1]); // Error

How to run select query with multiple bind variables?
for example:

SELECT
    id,
    username AS "nam",
    email AS "user_email",
    COUNT(*) AS "some_count"
FROM
    "users"
WHERE
    username = :_1_
    AND surname = :_2_
GROUP BY
    id,
    email

Empty EasyStatement clauses cause problems

Empty EasyStatements should stringify as "1", not an empty string. Consider:

$sth1 = \ParagonIE\EasyDB\EasyStatement::open();

$sth2 = \ParagonIE\EasyDB\EasyStatement::open();
$sth2->with("a=a");
$sth2->with($sth1);

print $sth2;
// -> a=a OR

Installation / use?

Hi,
can you maybe add a short installation how-to for people not using Composer? Not everyone has his/her own server, I'm using shared hosting.
Thanks.

Sanitation / input validation

In your example you used $_GET to make a SQL statement, Does this mean that you also perform input sanitation?
"SELECT * FROM comments WHERE blogpostid = {$_GET['blogpostid']} ORDER BY created ASC"

Connection issues

I can connect to my database using straight PDO. But, cannot connect using easydb. It gives PDO connection error. Check username & password. I am using V2.0.0 with Php 7.2. In addition, the error displays username and password and would like to suppress those.

Possible credentials leak

I was reached by a user concerned of the possible leak of the database credentials when an error occurs during Factory:create() call:

PHP Fatal error: Uncaught ParagonIE\EasyDB\Exception\ConstructorFailed: Could not create a PDO connection. Please check your username and password. in vendor/paragonie/easydb/src/Factory.php:77
Stack trace:
#0 0.php(10): ParagonIE\EasyDB\Factory::create('mysql:host=loca...', 'username', 'putastrongpassw...')
#1 {main}
thrown in vendor/paragonie/easydb/src/Factory.php on line 77

The problem is coming from the fact that Factory::create()'s parameters are listed in the stack trace.

I offered a user a quick and dirty solution of wrapping the call into a try catch and then re-throwing a generic exception that contains the error message from the caught exception.

But that's only a workaround and I think it would be better to change the Factory::create() method's signature. the simplest solution would be to make the method to accept an array of parameters instead of an explicit list of variables. This is against the best practices but here I would think it would be a good tradeoff between good practices and security.

I could send a pull request if you agree for this change.
Or we can try to find some other solution.

EasyStatement throws exception when the array is empty

Sometimes there is a need to build complex WHERE clauses and building the EasyStatement conditionally is not very convenient. If the values array is null EasyStatement should not add that WHERE clause to the SQL instead of letting PHP dump about incorrect number of arguments.

Example:

$dynamicArray = []; // Values are dynamically build somewhere else, but the array could also be empty
EasyStatement::open()->in('`Id` IN (?*)', $dynamicArray);

The result of this will be:

Warning: str_repeat(): Second argument has to be greater than or equal to 0 in ...EasyStatement.php on line 380

To avoid the exception I need to check if the array is empty before adding the next clause to EasyStatement

connection error displays username password

Connection error message shows username and password.
Running php 7.2, easydb 2.8.0.

How to get affected rows?

In safeQuery() the prepared statement exists only in the local scope, thus all high-level methods like insert, update, delete do not provide any means to obtains the affected rows. Or did I get something wrong?

Column Name

Are there any security risks if the form field name and mysql database column names are identical ?