Link to home
Create AccountLog in
Avatar of Richard Lloyd
Richard Lloyd

asked on

SQL curly quotes

I need to insert some text into an mssql database using PHP and sqlsrv and a parameterized query, but whenever there is a curly quote in the text it fails.


Error given is 


MESSSAGE: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated.

Open in new window

SQL: update table set otheranswer1=?, updateddate=getdate() where id='3373310'

Open in new window

PARAMS:: 'array ( 0 => \'I love cars. I’m the best big bro. \', )'

Open in new window

The curly ' in "I'm" is causing the problem.


How do I resolve this? Str_replace(... maybe?



Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

replace the open quote and close quote with the standard quote mark. 

I'd also replace the single quotes with double quotes.

The curly quote is probably utf-8 and is your sql server using utf-8 or 1252?

Avatar of Richard Lloyd
Richard Lloyd

ASKER

I had hoped that this would have worked, but unfortunately it does not


$value=str_replace(array('‘','’'),array(''','''), $value);

Open in new window


I see in the error that "ODBC Driver 17 for SQL Server" is used and there's a "Note that this driver supports SQL Server 2019 only from version 17.3. " so maybe check to see if you have compatible versions like SQL 2019 and at least ODBC 17.3. Aside that please check on the ODBC DSN config to see if you have Unicode selected under the Data Type Options.

To avoid this data loss when binding input parameters, specify a Unicode SQL character type such as SQL_NVARCHAR. In this case, the driver converts from the client encoding to UTF-16, which can represent all Unicode characters. Furthermore, the target column or parameter on the server must also be either a Unicode type (ncharnvarcharntext) or one with a collation/encoding, which can represent all the characters of the original source data. For avoiding data loss with output parameters, specify a Unicode SQL type, and either a Unicode C type (SQL_C_WCHAR), causing the driver to return data as UTF-16, or a narrow C type, and ensure that the client encoding can represent all the characters of the source data (this representation is always possible with UTF-8.)

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/programming-guidelines?view=sql-server-ver16 

ASKER CERTIFIED SOLUTION
Avatar of Richard Lloyd
Richard Lloyd

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account