Bug when creating a table if a field starts with the same chars using the function createTable.

[tyl3r]

function createTable ($name, $data) {
    global $db;
    //$q = "CREATE TABLE $name (id INT(9) UNSIGNED PRIMARY KEY NOT NULL";
    $db->rawQuery("DROP TABLE IF EXISTS $name");
    $q = "CREATE TABLE $name (id INT(9) UNSIGNED PRIMARY KEY AUTO_INCREMENT";
    foreach ($data as $k => $v) {
        $q .= ", $k $v";
    }
    $q .= ")";
    $db->rawQuery($q);
}

This fails:

'posts' => Array (
  'post_title' => 'VARCHAR(255) NOT NULL',
  'post_content' => 'TEXT NULL',
  'post_date' => 'DATETIME NOT NULL',
  'post_date_update' => 'DATETIME NOT NULL',
),

This is ok:

'posts' => Array (
  'post_title' => 'VARCHAR(255) NOT NULL',
  'post_content' => 'TEXT NULL',
  'post_date' => 'DATETIME NOT NULL',
  'post_update_date' => 'DATETIME NOT NULL',
),

[Mylife88]

am i blind? i see no difference between "This fails" and "This is ok"

[martyrayyy]

I notice they are the same. Try to set datetime default values.

'post_date' => 'DATETIME DEFAULT now()', 'post_update_date' => 'DATETIME DEFAULT NULL ON UPDATE now()',

[tyl3r]

Sorry, maybe didn't explain well:

@myLife88 As you can see the difference is only one field name: post_date_update vs post_update_date, the first one does not work because im also creating a field named post_date. Just try to create this table:

$prefix = 'test_';
$db->setPrefix( $prefix );
          
        $tables = Array (
              'posts' => Array (
              'post_title' => 'VARCHAR(255) NOT NULL',
              'post_content' => 'TEXT NULL',
              'post_date' => 'DATETIME NOT NULL',
              'post_date_update' => 'DATETIME NOT NULL',
        ),
); 

and you will see this error:

Fatal error: Uncaught Exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test_DATETIME NOT NULL, post_date_update test_DATETIME NOT NULL)' at line 1 query: CREATE TABLE test_posts (id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT, post_title VARCHAR(255) NOT NULL, post_content TEXT NULL, post_date test_DATETIME NOT NULL, post_date_update test_DATETIME NOT NULL) in PATH\inc\db_class.php on line 2013

@martyrayyy There is no problem with setting or not the DATETIME defaults, the problem is with in the fields names. The one you write works because you used post_update_date instead of post_date_update, change it and you will see the same error that i wrote above.

[RyadPasha]

This is caused because of the rawAddPrefix function which has many bugs, first thing is that only the first table in the query gets prefixed, (so if using query with a JOIN clause for example only first table will get prefixed), also a lot of statements such as (DROP TABLE, TRUNCATE TABLE, CREATE TABLE, LOCK TABLE, FLASHBACK TABLE, ALTER TABLE, ANALYZE TABLE, DESCRIBE and EXPLAIN) are not supported .. You can fix all these bug by replacing that function with mines:

/**
 * Prefix add raw SQL query.
 *
 * @author Mohamed Riyad <https://github.com/RyadPasha>
 * @param string $query User-provided query to execute.
 * @return string Contains the returned rows from the query.
 */
public function rawAddPrefix($query){
    $query = preg_replace(['/[\r\n]+/', '/\s+/'], ' ', $query); // Replace multiple line breaks/spaces with a single space
    if (preg_match_all("/(FROM|INTO|UPDATE|JOIN|DROP TABLE|TRUNCATE TABLE|CREATE TABLE|LOCK TABLE|FLASHBACK TABLE|ALTER TABLE|ANALYZE TABLE|DESCRIBE|EXPLAIN) [\\'\\´\\`]?(?!SELECT|DELETE|INSERT|REPLACE|UPDATE)([a-zA-Z0-9_-]+)[\\'\\´\\`]?/i", $query, $matches)) {
        for ($i = 0; $i < count($matches[0]); $i++) {
            list($from_table, $from, $table) = $matches;
            $query = str_replace($table[$i], self::$prefix.$table[$i], $query);
        }
    }
    return $query;
}