String column defaults are not properly escaped #56124
Description
Laravel Version
11.44.1 (also in newer branches)
PHP Version
8.4.6 (PHP version is not a factor)
Database Driver & Version
MariaDB 10.11.13 on Ubuntu
Description
The Illuminate Database toolset does not properly escape string-based column defaults when creating or modifying table columns. Literal values are enclosed in ', but any ' characters appearing in the default value will cause a SQL syntax error, unless they are manually escaped by the caller.
Steps To Reproduce
Test script:
<?php
require_once('vendor/autoload.php');
use Illuminate\Database\Capsule\Manager as Capsule;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Query\Expression;
$capsule = new Capsule;
$capsule->addConnection([
'driver' => 'mariadb', // For testing's sake -- this is not MariaDB-specific.
'host' => '127.0.0.1',
'database' => 'DATABASE_NAME_HERE',
'username' => 'USERNAME_HERE',
'password' => 'PASSWORD_HERE',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
]);
$capsule->setAsGlobal();
$schema = $capsule->schema();
// Clean up from a previous run if necessary, and create a table for testing purposes.
if ($schema->hasTable('test_table')) $schema->drop('test_table');
$schema->create('test_table', function (Blueprint $table) {
$table->string('test_string');
});
// Change the column default value to a string. This works as expected.
$schema->table('test_table', function (Blueprint $table) {
$table->string('test_string')->default('this will work')->change();
});
// Change the column default value to a string containing an apostrophe using an Expression,
// taking care of escaping ourselves. This works as expected.
$schema->table('test_table', function (Blueprint $table) {
$table->string('test_string')->default(new Expression('\'this\'\'ll work too\''))->change();
});
// Now, try providing the apostrophe containing string literal directly to Table::default(). This will break.
$schema->table('test_table', function (Blueprint $table) {
$table->string('test_string')->default('this\'ll break it')->change();
});Expected behaviour: No output; table created in DB with expected default.
Actual behaviour: Throws an exception...
PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'break it'' at line 1 in .../laravel/framework/src/Illuminate/Database/Connection.php:565
Stack trace:
#0 .../laravel/framework/src/Illuminate/Database/Connection.php(565): PDO->prepare()
#1 .../laravel/framework/src/Illuminate/Database/Connection.php(812): Illuminate\Database\Connection->{closure:Illuminate\Database\Connection::statement():560}()
#2 .../laravel/framework/src/Illuminate/Database/Connection.php(779): Illuminate\Database\Connection->runQueryCallback()
#3 .../laravel/framework/src/Illuminate/Database/Connection.php(560): Illuminate\Database\Connection->run()
#4 .../laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(118): Illuminate\Database\Connection->statement()
#5 .../laravel/framework/src/Illuminate/Database/Schema/Builder.php(564): Illuminate\Database\Schema\Blueprint->build()
#6 .../laravel/framework/src/Illuminate/Database/Schema/Builder.php(406): Illuminate\Database\Schema\Builder->build()
#7 .../test.php(44): Illuminate\Database\Schema\Builder->table()
#8 {main}
Next Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'break it'' at line 1 (Connection: default, SQL: alter table `test_table` modify `test_string` varchar(255) not null default 'this'll break it') in .../laravel/framework/src/Illuminate/Database/Connection.php:825
Stack trace:
#0 .../laravel/framework/src/Illuminate/Database/Connection.php(779): Illuminate\Database\Connection->runQueryCallback()
#1 .../laravel/framework/src/Illuminate/Database/Connection.php(560): Illuminate\Database\Connection->run()
#2 .../laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(118): Illuminate\Database\Connection->statement()
#3 .../laravel/framework/src/Illuminate/Database/Schema/Builder.php(564): Illuminate\Database\Schema\Blueprint->build()
#4 .../laravel/framework/src/Illuminate/Database/Schema/Builder.php(406): Illuminate\Database\Schema\Builder->build()
#5 .../test.php(44): Illuminate\Database\Schema\Builder->table()
#6 {main}
thrown in .../laravel/framework/src/Illuminate/Database/Connection.php on line 825
The issue is in src/Illuminate/Database/Schema/Grammars/Grammar.php in the getDefaultValue function, which simply drops a string literal between single quotes:
/**
* Format a value so that it can be used in "default" clauses.
*
* @param mixed $value
* @return string
*/
protected function getDefaultValue($value)
{
if ($value instanceof Expression) {
return $this->getValue($value);
}
if ($value instanceof BackedEnum) {
return "'{$value->value}'";
}
return is_bool($value)
? "'".(int) $value."'"
: "'".(string) $value."'";
}Proposed solution: when supplying a string literal, escaping should be taken care of by Laravel.