How to store multi byte characters in SQL Server database using CodeIgniter

Question

I'm using MS SQL Server and CodeIgniter 2 with Active Record for a project I'm working on, and I just stumbled upon this issue:

When I submit a form that contains Chinese or Hindi characters, I store it in a table, and when I view it all I get are question marks. If I try English or Greek characters, everything seems to work fine.

The reason I believe this is something to do with the PHP I'm writing, is because if I copy-paste the chinese text directly in SQL Server Management Studio, all values are stored and displayed perfectly, both on the SQL Studio, and the web application.

These are the db settings I'm using:

$db['local']['dbdriver'] = 'sqlsrv';
$db['local']['dbprefix'] = '';
$db['local']['pconnect'] = FALSE;
$db['local']['db_debug'] = TRUE;
$db['local']['cache_on'] = FALSE;
$db['local']['cachedir'] = '';
$db['local']['char_set'] = 'utf8';
$db['local']['dbcollat'] = 'utf8_general_ci';
$db['local']['swap_pre'] = '';
$db['local']['autoinit'] = TRUE;
$db['local']['stricton'] = FALSE;

This is the structure of the table I'm testing on right now:

CREATE TABLE [dbo].[languages](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [language] [nvarchar](1024) NULL,
    [language_local] [nvarchar](1024) NULL,
    [lang_code] [nvarchar](100) NULL,
    [core] [bit] NULL,
 CONSTRAINT [PK_languages] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,         ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

And this is my charset encoding in config.php

$config['charset'] = 'utf-8';

New troubleshooting data

I tried to save the following string through my form: Iñtërnâtiônàlizætiøn

CodeIgniter replied with this error:

An error occurred translating the query string to UTF-16: No mapping for the Unicode character exists in the target multi-byte code page. .

This doesn't appear when I try to store Chinese characters Thank you in advance :)

Perhaps setting it in your PHP INI file will help you troubleshoot? ini_set('default_charset', 'UTF-8'); — Markus
– Markus, Commented Feb 7, 2012 at 15:28
Also on your webpages verify you are using the correct encoding. <meta http-equiv="content-type" content="text/html; charset=UTF-8"> — Markus
– Markus, Commented Feb 7, 2012 at 15:29
I wasn't aware of this setting in php! I'll check it tommorow and inform :) — Loupax
– Loupax, Commented Feb 7, 2012 at 16:28
It wasn't the setting... Also all my pages have the <meta> charset to UTF-8 :/ — Loupax
– Loupax, Commented Feb 8, 2012 at 9:03
You are using a class from CodeIgniter or some other API to interface with your db? Write a quick and mysqli connector and pass an SQL insert statement with the UTF-8 characters in the body of the form, to see if you can track it down to an issue with the API you are using. — Markus
– Markus, Commented Feb 8, 2012 at 17:19

Amin Adha · Accepted Answer · 2012-11-23 19:41:22Z

9

Try to convert your input with iconv() before insert to db :

$input = iconv('','UTF-8',$str);

answered Nov 23, 2012 at 19:41

Amin Adha

3163 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

low_rents Over a year ago

this is bad practice. if you have setup everything correctly (database-interface, encoding of your files), then you NEVER EVER need iconv() or any other encoding-converting function in PHP.

dbrumann · Accepted Answer · 2012-02-14 11:04:05Z

3

+50

Handling encoding in Microsoft's SQL Server from PHP can be quite painful. The CharacterSet-option was included with version 1.1 of Microsoft SQL Server Driver for PHP (SQLSRV), so there's an off-chance, you are using an outdated version that does not support setting the ChracterSet, although that is unlikely. Changing char_set to UTF-16 is not an option, as SQLSRV only supports UTF-8.

More likely one of the following applies:

in your php.ini the option default_charset is not set to UTF-8
as you probably are working on a Windows machine, your .php-file is not encoded in UTF-8.

If this does not solve the problem, then your input probably contains one ore more characters, which are not valid UTF-8. In this case try converting your (user) input with iconv().

edit: Regarding @Markus comment: CodeIgniter's system/database/drivers/sqlsrv/sqlsrv_driver.php looks like a simple wrapper around the sqlsrv-commands, it seems therefore unlikely, that the problem is caused by CodeIgniter-code.

edited Feb 14, 2012 at 11:04

answered Feb 14, 2012 at 10:56

dbrumann

17.2k2 gold badges47 silver badges63 bronze badges

2 Comments

Loupax Over a year ago

That indeed sounds like pain... In other words I'll have to check all my inputs if they contain UTF-16 characters, and if they do convert them to UTF-8 prior submitting to the database? Ouch... I'll try updating my sqlsrv driver and accept this answer once confirmed!

Diego Vieira Over a year ago

only converting using iconv as proposed by @Amin_Adha worked. Setting the default_charset makes no difference (I'm not using code igniter btw)

Loupax · Accepted Answer · 2017-06-06 12:05:20Z

Looks like this answer is getting a lot of attention, and I feel bad for not posting the actual solution to my problem... I'd guess it's bad etiquette to de-select an answer I selected many years ago so I won't for now. Here goes...

No changes needed to be done to the settings. The problem is query related, and unfortunately CodeIgniter doesn't support the proper query format out of the box.

So when you want to insert multibyte characters into your table, you have to prepend the character N before your string.

So in my example above the query should look like this in order to work

INSERT INTO test_table (title) VALUES (N'Iñtërnâtiônàlizætiøn')

No, CI doesn't currently give you a built in way to do this. It is planed to be added in on CI4, but until then here is a hack for you

Shahzad Barkati · Accepted Answer · 2015-08-23 13:42:10Z

0

I had this error:

Error Number: IMSSP

An error occurred translating the query string to UTF-16: No hay ninguna asignación en la página de códigos de múltiples bytes de destino para el carácter Unicode. .

And for me workrs with:

iconv('','utf-8',$pass);

before doing the query on the DB.

edited Aug 23, 2015 at 13:42

Shahzad Barkati

2,5506 gold badges28 silver badges34 bronze badges

answered Aug 23, 2015 at 12:53

diegolaprida

1

Comments

Ali · Accepted Answer · 2017-06-06 08:07:35Z

0

I had same problem but only htmlspecialchars() worked for me.

$value = htmlspecialchars($input)

answered Jun 6, 2017 at 8:07

Ali

1

1 Comment

Loupax Over a year ago

You really should not change the user input when storing it into the database.

mlinuxgada · Accepted Answer · 2012-02-14 08:04:46Z

-2

Try with utf-16 encoding, both in :

$config['charset'] = 'utf-16';

and

$db['local']['char_set'] = 'utf16';

And also the db encoding must be set properly as utf-16.

answered Feb 14, 2012 at 8:04

mlinuxgada

5903 silver badges7 bronze badges

1 Comment

Loupax Over a year ago

These settings make me unable to connect to my database. The problem is that there is no way of setting manually the default collation of MSSQL... Not in the same way I can in MySQL at least...

Collectives™ on Stack Overflow

How to store multi byte characters in SQL Server database using CodeIgniter

6 Answers 6

1 Comment

2 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related