last() failes while quoting integer

axelhahn commented 3 years ago

Information

Version of Medoo: 2.1.2
Type of Database (MySQL, MSSQL, SQLite...): MySQL
System (Liunx\Windows\Mac): Win

Describe the Problem After migrating from 1.x to 2.1,2 the method last() does not work (in my app) anymore. It occurs on PHP 7.4 and 8.0.

Detail Code after an insert (it was successful) I use echo $oDB->last();

Stack trace:
#0 PATH\medoo\src\Medoo.php(607): Medoo\Medoo->quote(33427)
#1 PATH\medoo\src\Medoo.php(2190): Medoo\Medoo->generate('INSERT INTO `pa...', Array)
#2 PATH\classes\crawler-base.class.php(855): Medoo\Medoo->last()

In #0 the problem is shown: the quote() method got an integer called in generate(). The value 33427 is a value in a databes column "size" which has type "int".

            $aResult = $this->oDB->insert(
                    'pages', 
                    array(
                        'url' => $aData['url'],
                        'siteid' => $this->iSiteId,
                        'title' => $aData['title'],
                        'title_wc' => $this->_getWordCount($aData['title']),
                        'description' => $aData['description'],
                        'description_wc' => $this->_getWordCount($aData['description']),
                        'keywords' => $aData['keywords'],
                        'keywords_wc' => $this->_getWordCount($aData['keywords']),
                        'lang' => $aData['lang'],
                        'size' => $aData['size'],
                        'time' => $aData['time'],
                        'content' => html_entity_decode($aData['content']),
                        'header' => json_encode($aData['header']), // TODO: handle umlauts in response
                        'response' => $aData['response'],
                        'ts' => date("Y-m-d H:i:s"),
                        'tserror' => '0000-00-00 00:00:00',
                        'errorcount' => 0,
                    )
            );

In the source of generate() the value PDO::PARAM_STR seems to catch integer values. In generate the integer values must be handled before a string - or the qote() method must detect an integer. My fast fix was adding a test is_integer($value[1]) in the generate() method before handling strings.

protected function generate(string $statement, array $map): string
{
...
        foreach ($map as $key => $value) {
            // Fast fix: adding is_integer()
            if (is_integer($value[1])) {
                $replace = $value[0] . '';
            } elseif ($value[1] === PDO::PARAM_STR) {
                $replace = $this->quote($value[0]);
            } elseif ($value[1] === PDO::PARAM_NULL) {
                $replace = 'NULL';
            } elseif ($value[1] === PDO::PARAM_LOB) {
                $replace = '{LOB_DATA}';
            } else {
                $replace = $value[0] . '';
            }

Expected output Get a string with last query.

axelhahn commented 2 years ago

I dived a bit deeper into what happens in the generate() method. Its 2nd param $map comes from $log of executed queries. There is a helper method Medoo\Medoo::typeMap().

I added a line debug code in to see my values of the query in typeMap() while adding an indexed website into the database (= the values of the given insert in the 1st posting). echo __METHOD__."(".(is_string($value)? substr($value, 0, 20) : $value ).", $type)\n"; shows me

Medoo\Medoo::typeMap(https://www.axel-hah, string)
Medoo\Medoo::typeMap(1, string)
Medoo\Medoo::typeMap(Startseite, string)
Medoo\Medoo::typeMap(1, integer)
Medoo\Medoo::typeMap(Willkommen auf der p, string)
Medoo\Medoo::typeMap(23, integer)
Medoo\Medoo::typeMap(, string)
Medoo\Medoo::typeMap(0, integer)
Medoo\Medoo::typeMap(de, string)
Medoo\Medoo::typeMap(32905, double)  <<<<<<<<<< the troublemaker - value for column "size"
Medoo\Medoo::typeMap(134, integer)
Medoo\Medoo::typeMap( Startseite Axels We, string)
Medoo\Medoo::typeMap({"url":"https:\/\/ww, string)
Medoo\Medoo::typeMap(<!DOCTYPE html><htm, string)
Medoo\Medoo::typeMap(2021-10-10 19:48:26, string)
Medoo\Medoo::typeMap(0000-00-00 00:00:00, string)
Medoo\Medoo::typeMap(0, integer)

The type double is associated to PDO::PARAM_STR - thats why the generator() wants to quote it. Yan you verify this line (it replaces the fast fix above): https://github.com/catfan/Medoo/blob/master/src/Medoo.php#L747

How about setting that line to 'double' => PDO::PARAM_INT ? This works for me in current version 2.1.3.

regards, Axel

axelhahn commented 2 years ago

added https://github.com/catfan/Medoo/pull/1020

catfan / Medoo

last() failes while quoting integer #1011