How to store multibyte characters in a SQL Server database using CodeIgniter

Question

I am using MS SQL Server and CodeIgniter 2 with Active Record for a project that I am working on and I just stumbled upon this problem:

When I submit a form containing Chinese or Hindi characters, I store it in a table, and when I look through it all I get is question marks. If I try English or Greek characters, everything seems to be working fine.

The reason I think this is due to the PHP that I am writing is because if I copy Chinese text directly to SQL Server Management Studio, all values ​​are stored and displayed perfectly, both in SQL Studio and web Appendix.

These are the db settings I use:

$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 table structure that I'm testing 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 encoding encoding in config.php

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

New troubleshooting information

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

CodeIgniter responded 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 does not appear when I try to store Chinese characters Thank you in advance :)

Answer 1

This answer seems to get a lot of attention, and I feel bad not to publish the actual solution to my problem ... I would guess that it is bad etiquette to cancel the answer that I chose many years ago, so I haven’t I will do it. Here goes ...

No changes to the settings are required. The problem is with requests, and unfortunately, CodeIgniter does not support the correct request format.

So, if you want to insert multibyte characters into your table, you must add the character N before your row.

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

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

No, CI currently does not give you a built-in way to do this. It is planned to be added on CI4, but until then, here's a hack for you

Answer 2

Try converting your input using the () icon before pasting into db:

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

Answer 3

Encoding processing in Microsoft SQL Server with PHP can be quite painful. The CharacterSet option was included in version 1.1 for Microsoft SQL Server Driver for PHP (SQLSRV), so it is likely that you are using an older version that does not support installing ChracterSet, although this is unlikely. Changing char_set to UTF-16 is not an option since SQLSRV only supports UTF-8.

Most likely one of the following statements:

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

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

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

Answer 4

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 for el carácter Unicode ..

And for me workrs with:

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

before executing a query in the database.

Answer 5

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

 $value = htmlspecialchars($input) 

Answer 6

Try using utf-16 encoding, as in:

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

and

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

And also db coding should be set correctly as utf-16.