Posted on 2006-06-02
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@example.com', '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.