CODEWITHSUNDEEP
typo3query-builder
Franz Holzinger
Franz Holzinger

Reputation: 998

TYPO3 Querybuilder unsigned integer in createNamedParameter

The TYPO3 QueryBuilder method createNamedParameter does only support a signed integer but no unsigned integer. There is not even a PDO constant for an unsigned integer. And TYPO3 seems to have no other constants like for the arrays Connection::PARAM_INT_ARRAY.

static public function checkDoublePostExist ($table, $doublePostField, $key)
{
    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
    $queryBuilder->setRestrictions(GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\Query\Restriction\FrontendRestrictionContainer::class));

    $result =
        $queryBuilder
            ->count('*')
            ->from($table)
            ->where(
                $queryBuilder->expr()->eq($doublePostField, $queryBuilder->createNamedParameter($key, \PDO::PARAM_INT))
                )
            ->execute()
            ->fetchColumn(0);

    return $result;
}

The SQL field is defined as:

doublePostCheck int(11) unsigned DEFAULT '0' NOT NULL

The generated SQL will be like this:

SELECT COUNT(*) FROM `tt_board` WHERE (`doublePostCheck` = -1018532669) AND (`tt_board`.`deleted` = 0)

This is weird, because no negative values are allowed for this field. I am afraid that this query will not do the right thing.

Can the string format be used for an unsigned integer?

                ->where(
                $queryBuilder->expr()->eq($doublePostField, $queryBuilder->createNamedParameter($key, \PDO::PARAM_STR))
                )

The generated SQL would be correct:

SELECT COUNT(*) FROM `tt_board` WHERE (`doublePostCheck` = '3276434627') AND (`tt_board`.`deleted` = 0)

Which solution is the best for unsigned integers?

Upvotes: 1

Views: 552

Answers (1)

Thomas Löffler
Thomas Löffler

Reputation: 6164

The problem is that your integer value is higher as the field int(11) can work with.

The maximum value for unsigned fields is 2147483647, see https://dev.mysql.com/doc/refman/8.0/en/integer-types.html. Your value (3276434627) is quite higher than this.

As PHP's integer maximum value is the same, it begins to start in negative area when the value is higher than maximum value.

The solution would be to change the database field to use bigint.

Upvotes: 0

Related Questions