Php sql server character set

PHP + SQL Server — How to Set Charset for Connection

PHP + SQL Server — How to set charset for connection?

Client charset is necessary but not sufficient:

I searched for two days how to insert UTF-8 data (from web forms) into MSSQL 2008 through PHP. I read everywhere that you can’t, you need to convert to UCS2 first (like cypher’s solution recommends).
On Windows SQLSRV said to be a good solution, which I couldn’t try, since I am developing on Mac OSX.

However, FreeTDS manual (what PHP mssql uses on OSX) says to add a letter «N» before the opening quote:

mssql_query("INSERT INTO table (nvarcharField) VALUES (N'űáúőűá球最大的采购批发平台')", +xon);

According to this discussion, N character tells the server to convert to Unicode.
https://softwareengineering.stackexchange.com/questions/155859/why-do-we-need-to-put-n-before-strings-in-microsoft-sql-server

Encoding problem when connecting to SQL Server database via odbc_connect()

This whole encoding thing still feels chaotic to me, but I somehow managed to do it. It works when:

• odbc connection string contains charset=cp1250
• PHP header character set is set to utf-8
• I convert all problematic strings from cp1250 to utf-8 with iconv

How to set charset in mssql_connect?

Finally I did it.
I have used ODBC Driver for connecting to SQL Server and then do the SELECT .

$pdo = new PDO ("odbc:Driver=;Server=$hostname;Database=$dbname; Uid=$username;Pwd=$pw;");

Note: I did this connection in my test environment (Windows 7) and not on
FreeBSD.

SQL result not english characters

I know, that this answer is posted too late, but I am in similar situation these days, so I want to share my experience.
My configuration is almost the same — database and table columns with Cyrillic_General_CS_AS collation. Note, that I use PHP Driver for SQL Server, not build-in ODBC support.

The steps below have helped me to resolve my case. I’ve used collation from your example.

CREATE TABLE [dbo].[MyTable] ( 
[TextInSpanish] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL, 
[NTextInSpanish] [nvarchar](50) COLLATE Modern_Spanish_CI_AS NULL
)

INSERT [dbo].[MyTable] (TextInSpanish, NTextInSpanish) 
VALUES ('Algunas palabras en español', N'Algunas palabras en español')

Set default_charset = «UTF-8» in your php.ini file.
Encode your source files in UTF-8. I use Notepad++ for this step.

Read data from database:

• With default connection encoding. For reading data from database use $data = iconv(‘CP1252’, ‘UTF-8’, $data);
  Note, that by default data is returned in 8-bit characters as specified in the code
  page of the Windows locale that is set on the system. Any
  multi-byte characters or characters that do not map into
  this code page are substituted with a single-byte question
  mark (?) character. This is the default encoding.
• With UTF-8 connection encoding.
  Column must be of type ‘nchar’ or ‘nvarchar’.

Working Example:

test.php (PHP 7.1, PHP Driver for SQL Server 4.3, file test.php is UTF-8 encoded):

// Connection settings
$server = ‘127.0.0.1\instance,port’;
$database = ‘database’;
$user = ‘username’;
$password = ‘password’;
$cinfo = array(
«CharacterSet»=>SQLSRV_ENC_CHAR,
#»CharacterSet»=>»UTF-8″,
«Database»=>$database,
«UID»=>$user,
«PWD»=>$password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false)
echo «Error (sqlsrv_connect): «.print_r(sqlsrv_errors(), true);
exit;
>

// Query
$sql = «SELECT * FROM MyTable»;
$res = sqlsrv_query($conn, $sql);
if ($res === false) echo «Error (sqlsrv_query): «.print_r(sqlsrv_errors(), true);
exit;
>

// Results
while ($arr = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC)) # Use next 2 lines with «CharacterSet»=>SQLSRV_ENC_CHAR connection setting
echo iconv(‘CP1252’, ‘UTF-8’, $arr[‘TextInSpanish’]).»
«;
echo iconv(‘CP1252’, ‘UTF-8’, $arr[‘NTextInSpanish’]).»
«;
# Use next 2 lines with «CharacterSet»=>»UTF-8» connection setting
#echo $arr[‘TextInSpanish’].»
«;
#echo $arr[‘NTextInSpanish’].»
«;
>

// End
sqlsrv_free_stmt($res);
sqlsrv_close($conn);
?>

PHP — Fetching UTF-8 data from sqlsrv database

Set the database collation to utf8_general_ci

Getting data with UTF-8 charset from MSSQL server using PHP FreeTDS extension

MSSQL and UTF-8 are quite a pain in the . sometimes. I had to convert it manually.
The problem: MSSQL doesn’t actually know and support UTF-8.

Convert from database value to UTF-8:

mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8' ? $value : mb_convert_encoding($value, 'UTF-8');

Converting from UTF-8 to database value:

mb_convert_encoding($value, 'UCS-2LE', mb_detect_encoding($value, mb_detect_order(), true));

Fortunately I was using Doctrine so all I had was to create a custom StringType implementation.

Источник

The character set and character escaping

The character set should be understood and defined, as it has an affect on every action, and includes security implications. For example, the escaping mechanism (e.g., mysqli_real_escape_string() for mysqli and PDO::quote() for PDO_MySQL) will adhere to this setting. It is important to realize that these functions will not use the character set that is defined with a query, so for example the following will not have an effect on them:

Example #1 Problems with setting the character set with SQL

$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

// Will NOT affect $mysqli->real_escape_string();
$mysqli -> query ( «SET NAMES utf8mb4» );

// Will NOT affect $mysqli->real_escape_string();
$mysqli -> query ( «SET CHARACTER SET utf8mb4» );

// But, this will affect $mysqli->real_escape_string();
$mysqli -> set_charset ( ‘utf8mb4’ );

// But, this will NOT affect it (UTF-8 vs utf8mb4) — don’t use dashes here
$mysqli -> set_charset ( ‘UTF-8’ );
?>

Below are examples that demonstrate how to properly alter the character set at runtime using each API.

Note: Possible UTF-8 confusion

Because character set names in MySQL do not contain dashes, the string «utf8» is valid in MySQL to set the character set to UTF-8 (up to 3 byte UTF-8 Unicode Encoding). The string «UTF-8» is not valid, as using «UTF-8» will fail to change the character set and will throw an error.

Example #2 Setting the character set example: mysqli

$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

echo ‘Initial character set: ‘ . $mysqli -> character_set_name () . «\n» ;

if (! $mysqli -> set_charset ( ‘utf8mb4’ )) printf ( «Error loading character set utf8mb4: %s\n» , $mysqli -> error );
exit;
>

echo ‘Your current character set is: ‘ . $mysqli -> character_set_name () . «\n» ;
?>

Example #3 Setting the character set example: pdo_mysql

Источник