iqbalrafli13's avatar

Emoji or Unicode characters break when saving via Eloquent to SQL Server NVARCHAR – works with Query Builder

Hi everyone, I’m running into a strange problem with Unicode (emoji) characters not saving correctly when using Eloquent in Laravel. I’m trying to save emoji characters like ✅ into a SQL Server NVARCHAR column.

  • If I use Laravel’s query builder, everything works:
DB::table('cabang')->insert(['kodecabang' => '✅']);

The emoji gets saved and displays correctly.

  • But if I use Eloquent:
$cabang = new \App\Models\Cabang();
$cabang->kodecabang = '✅';
$cabang->save();

The character is either stored as gibberish (Œ, ?, etc) or I get an error like:

Malformed UTF-8 characters, possibly incorrectly encoded

What I’ve Tried

  • The column is NVARCHAR, not VARCHAR
  • Database collation is the default (SQL_Latin1_General_CP1_CI_AS)
  • I validated the input with mb_check_encoding() and even tried iconv()
  • Tried intercepting saving() model event to clean inputs
  • Added middleware to enforce UTF-8
  • Configured Laravel connection 'charset' => 'utf8' for sqlsrv
  • All PHP files are saved as UTF-8 (without BOM)
  • Using PHP 7.4.9 on Windows Server 2012 R2
  • Laravel version is 8.x

Interestingly, the same exact code works on my local machine (Windows 10), but fails on the production server (Windows Server 2012 R2). Both use the same Laravel version and database driver (pdo_sqlsrv), and both show no ExtensionVer when I run php -i | find "sqlsrv".

My Assumptions

From what I understand:

  • Eloquent uses bound parameters (not raw strings), so it doesn't send N'...' by default
  • pdo_sqlsrv needs to properly handle Unicode to bind strings to NVARCHAR
  • The driver on the server may not support this properly (even though it doesn’t throw errors)
  • Using Query Builder bypasses this by sending raw data

The Questions

  • Has anyone encountered this issue before with SQL Server and Eloquent?
  • Is it possible to configure Laravel to always bind NVARCHAR properly with Unicode?
  • Is the problem most likely in the version of pdo_sqlsrv or the OS itself?
  • Any way to force Laravel/Eloquent to treat bound strings as Unicode (or prepend N somehow)?
  • Is switching to the official Microsoft SQLSRV driver the only clean solution?

Thanks so much in advance — I’ve been stuck on this for days.

0 likes
4 replies
Braunson's avatar
  • What's the exact pro_sqlsrv version on local vs prod? php -m | grep sqlsrv
  • Are you using any mutations/casts on t he kodecabang attribute?
  • What's your exact connection charset config (config/database.php) for your sqlsrv connection?

You can try forcing Unicode in connection (in config/database.php in your options..)

'sqlsrv' => [
    // ... other config
    'options' => [
        PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8,
        'CharacterSet' => 'UTF-8'
    ]
],

Alternatively you can use a model mutator as a workaround in your Cabang.php model:

public function setKodecabangAttribute($value)
{
    // Force UTF-8 encoding
    $this->attributes['kodecabang'] = mb_convert_encoding($value, 'UTF-8', 'UTF-8');
}
nara014's avatar

Any update about this? This problem was have the solution?

nara014's avatar

i think i know the solution, you need to change collation to Latin1_General_100_CI_AS_SC

Please or to participate in this conversation.