• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 534
  • Last Modified:

Enclosed strings

My application formats sql strings and sends them off to a mYsql server - this works fine.

However, I need now to pass the same sql strings to a mssql server but there is a problem.  SOME of the sql strings use double quotes instead of single quotes to enclose substrings inside the sql string.  Mssql only accepts single quotes.  For example, I have an error:

Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '1'.
SQL: SELECT site_id FROM proteus_vsa.dbo.sites WHERE site_id="1";

This does not work, but in cutting the sql string out and pasting into mssql query analyzer, I found this to work:

SELECT site_id FROM proteus_vsa.dbo.sites WHERE site_id='1';

As you can see, the site_id value is now enclosed in single quotes rather than double quotes, and the query runs fine.  Does anyone know the rules around quotes for sql that I might be missing?  I have acceess to this variable in php as $sql, which I need to parse to remove the strings enclosed in double quotes and replace them with strings enclosed in single quotes.  The hundreds of $sql variables that I build in my application are of all types of query, select, delete, update, joins etc.  I need to parse each query, and make sure that the quotes are of the correct type.  However, substrings passed as field data in the sql string cant be affected.  For example if I have a query:

INSERT INTO user_log VALUES (1, 'email@domain.co.uk', 'A_COKER', '192.168.0.2', 'there are lots of string\'s inside my sql string\'s with quotes in');

Obviously I need the quotes inside the field data to be ignored, ane queries that are already in the correct format to remain unchanged.

Also if anyone knows of any issues that I may encounter converting sql strings in such a way, or even issues where sql strings will work with mYsql and not with mSsql then please let me know.

Thanks

Ashley
0
jbclelland
Asked:
jbclelland
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
double quotes in sql server are by default delimiters for column and table names, hence the first error message.
you might look into "SET QUOTED_IDENTIFIER OFF"  to change that behaviour

single quotes are string delimiters, and if you need to have 1 quote in the string, duplicate it:

INSERT INTO user_log VALUES (1, 'email@domain.co.uk', 'A_COKER', '192.168.0.2', 'there are lots of string''s inside my sql string''s with quotes in');
0
 
jbclellandAuthor Commented:
How do I set SET QUOTED_IDENTIFIER OFF?

Also, our application runs at client sites on a mSsql server with multiple database on it.  For this to be an option I would need it to not effect the server as it is at the moment.  Is this a global server setting?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
levyukCommented:
Couldn't you just do this
$sql = "your string with double quotes";
$sql = str_replace(""", "'", $sql);

I think that works, it will replace all double quotes with single quotes
0
 
Richard QuadlingSenior Software DeverloperCommented:
You can just add the statement to the SQL ...

SET QUOTED_IDENTIFIER OFF;
SELECT ...
SET QUOTED_IDENTIFIER ON;

This is from MSBOL ...

SET QUOTED_IDENTIFIER
Causes Microsoft® SQL Server™ to follow the SQL-92 rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not usually allowed by the Transact-SQL syntax rules for identifiers.

Syntax
SET QUOTED_IDENTIFIER { ON | OFF }

Remarks
When SET QUOTED_IDENTIFIER is ON (default), identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information, see Using Identifiers. Literals can be delimited by either single or double quotation marks.

When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not usually allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks ("). SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when creating or manipulating indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns, see Considerations When Using SET Statements in SET.

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or OLE DB connection properties. SET QUOTED_IDENTIFIER defaults to OFF for connections from DB-Library applications.

When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's meta data even if the option is set to OFF when the table is created.

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.

When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.

SET QUOTED_IDENTIFIER also corresponds to the quoted identifier setting of sp_dboption. If SET QUOTED_IDENTIFIER is OFF, SQL Server uses the quoted identifier setting of sp_dboption. For more information about database settings, see sp_dboption and Setting Database Options.

SET QUOTED_IDENTIFIER is set at parse time. Setting at parse time means that if the SET statement is present in the batch or stored procedure, it takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed.
0
 
Richard QuadlingSenior Software DeverloperCommented:
B. Use the quoted identifier setting with single and double quotes
This example shows the way single and double quotation marks are used in string expressions with SET QUOTED_IDENTIFIER set to ON and OFF.

SET QUOTED_IDENTIFIER OFF
GO
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'Test')
   DROP TABLE Test
GO
USE pubs
CREATE TABLE Test ( Id int, String varchar (30) )
GO

-- Literal strings can be in single or double quotation marks.
INSERT INTO Test VALUES (1,"'Text in single quotes'")
INSERT INTO Test VALUES (2,'''Text in single quotes''')
INSERT INTO Test VALUES (3,'Text with 2 '''' single quotes')
INSERT INTO Test VALUES (4,'"Text in double quotes"')
INSERT INTO Test VALUES (5,"""Text in double quotes""")
INSERT INTO Test VALUES (6,"Text with 2 """" double quotes")
GO

SET QUOTED_IDENTIFIER ON
GO

-- Strings inside double quotation marks are now treated
-- as object names, so they cannot be used for literals.
INSERT INTO "Test" VALUES (7,'Text with a single '' quote')
GO

-- Object identifiers do not have to be in double quotation marks
-- if they are not reserved keywords.
SELECT *
FROM Test
GO

DROP TABLE Test
GO

SET QUOTED_IDENTIFIER OFF
GO

Here is the result set:

Id          String                        
----------- ------------------------------
1           'Text in single quotes'        
2           'Text in single quotes'        
3           Text with 2 '' single quotes  
4           "Text in double quotes"        
5           "Text in double quotes"        
6           Text with 2 "" double quotes  
7           Text with a single ' quote    
0
 
Richard QuadlingSenior Software DeverloperCommented:
Hmmm...

SET QUOTED_IDENTIFIER ON
GO

-- Strings inside double quotation marks are now treated
-- as object names, so they cannot be used for literals.
INSERT INTO "Test" VALUES (7,'Text with a single '' quote')
GO


This suggests that turning QUOTED_IDENTIFIER to ON is NOT what you want as "xxx" means xxx is now an object and NOT a string.

For a long term goal, you may need to convert your SQL statements into something like ...

$sQ = is_using_mssql_test_here : '"' : "'";
"SELECT site_id FROM proteus_vsa.dbo.sites WHERE site_id={$sQ}1{$sQ}";

Eek.

Or use a db abstraction class where you define things more rationally than just a SQL string.

Or use a template system where you define different versions external to the main script and load in the appropriate template.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now