Escape double quotes in SQL 2005/2008

Question

I recently have an international company that was named "BLA" BLAHBLAH "(double quotes are part of the name.)

Whenever a user tries to find this company by entering "Blah" or something like that, the search is not performed with a syntax error on the SQL server.

How can I avoid this so that the search is not interrupted?

SQL example:

SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect FROM RussoundGeneral.dbo.Company c LEFT JOIN RussoundGeneral.dbo.CompanyMax cm ON (cm.companyId = c.companyId and cm.maxID is not null) WHERE CONTAINS ( companyName, '"BLAH*' ) GROUP BY c.companyID, c.companyName, c.dateAdded ORDER BY c.companyName ASC 

Answer 1

Unfortunately, double quotes have a special meaning inside FTI, so even if you parameterize it, the FTI engine treats it as a phrase delimiter. I'm not sure if there is an easy way to include double quotes in the FTI search. Brackets are also a special character, but can be quoted for processing as a query condition, but not for AFAIK double quotes.

Update

A bit of searching suggests that doubling the quote on "" can fix it - it's worth a try. Personally, I would do this inside the database, as this is a part of TSQL implementation.

Likewise, “need to double to” before moving on to FTI (completely divided by TSQL escaping),

Answer 2

Use a parameterized query and all your problems with quotes will go away.

Edit: if you do not allow them to enter more than one word in CONTAINS, sanitize the parameter by removing the quotation marks. Sanitizing input by removing quotes can work anyway, regardless of multiple word searches.

Answer 3

I strongly suspect that you are building SQL dynamically - for example.

 // Bad code, do not use! string sql = "SELECT * FROM Foo WHERE X LIKE '" + input + "%'"; 

This is really a very bad idea for many reasons - primarily SQL injection attacks . Instead, use parameterized SQL statements, where you specify parameters separately.

Take a look at the various answers to the questions with the sql-injection tag for examples of how to do this correctly.

Answer 4

This is a bit theoretical answer, but maybe it will help. The short version is "using parameters in a query", but it helps to understand all the details.

In standard SQL, strings are enclosed in single quotes, and embedded single quotes are represented by two single quotes in a string:

 SELECT * FROM SomeWhere WHERE SomeThing = 'He said, "Don''t do it!"'; 

In some dialects of SQL, you can instead close double-quoted strings; you need to double double quotes to insert one instance of double quotes:

 SELECT * FROM SomeWhere WHERE SomeThing = "He said, ""Don't do it!""'; 

It is unclear whether the company name refers to external double quotes, as well as to the middle, or simply contains the middle. However, in principle, the rules are the same. Assuming all three double quotes are necessary, and using single quotes in SQL is much simpler in this context:

 SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect FROM RussoundGeneral.dbo.Company c LEFT JOIN RussoundGeneral.dbo.CompanyMax cm ON (cm.companyId = c.companyId and cm.maxID is not null) WHERE CONTAINS ( companyName, '"BLAH "BLAHBLAH" Ltd.' ) GROUP BY c.companyID, c.companyName, c.dateAdded ORDER BY c.companyName ASC; 

Using double quotes:

 SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect FROM RussoundGeneral.dbo.Company c LEFT JOIN RussoundGeneral.dbo.CompanyMax cm ON (cm.companyId = c.companyId and cm.maxID is not null) WHERE CONTAINS ( companyName, """BLAH ""BLAHBLAH"" Ltd." ) GROUP BY c.companyID, c.companyName, c.dateAdded ORDER BY c.companyName ASC; 

If you are building strings in a programming language, you have to worry about getting these quotes for everything that evaluates strings in your programming language. For example, if you were building a string literal in C, you would need to avoid double quotes with backslashes:

 static const char sql_stmt[] = "SELECT c.companyID, c.companyName, c.dateAdded,\n" " COUNT(cm.maxID) AS NumDirect\n" " FROM RussoundGeneral.dbo.Company c\n" " LEFT JOIN RussoundGeneral.dbo.CompanyMax cm\n" " ON (cm.companyId = c.companyId AND cm.maxID IS NOT NULL)\n" " WHERE CONTAINS(companyName, \"\"\"BLAH \"\"BLAHBLAH\"\" Ltd.\")\n" " GROUP BY c.companyID, c.companyName, c.dateAdded\n" " ORDER BY c.companyName ASC"; 

On the other hand, if you are reading data from a user - for example, the name of the company, then you just need to make sure that what you read is correctly indicated.

Those who said "use parameters" are correct - it is much simpler and more reliable and less vulnerable to SQL injection (see XKCD if you still do not see this). But if you understand the basics, you can adapt to the real requirements of your system.

Final note: in standard SQL, double quotation marks enclose "separable identifiers." That is, double quotes surround the name, which should be considered as the name of something in the database, and not as a string literal. In MS SQL Server, [square brackets] serve the same purpose; what is between the brackets is the name of the column or something inside the database. Many systems are more flexible than this; not all systems are the same in how they deviate from the standard.

Answer 5

Have you tried replacing a character with an ASCII character?

Answer 6

Try using the escape keyword:

 SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect FROM RussoundGeneral.dbo.Company c LEFT JOIN RussoundGeneral.dbo.CompanyMax cm ON (cm.companyId = c.companyId and cm.maxID is not null ) WHERE CONTAINS ( companyName, '\"BLAH*' ) escape '\' group by c.companyID, c.companyName, c.dateAdded ORDER BY c.companyName ASC 

Answer 7

should be something like

 string sqlCommand = "SELECT c.companyID, c.companyName, c.dateAdded, count(cm.maxID) as NumDirect FROM RussoundGeneral.dbo.Company c LEFT JOIN RussoundGeneral.dbo.CompanyMax cm ON (cm.companyId = c.companyId and cm.maxID is not null ) WHERE CONTAINS ( companyName, '@strVal' ) group by c.companyID, c.companyName, c.dateAdded ORDER BY c.companyName ASC" SqlCommand command = new SqlCommand(strSQLCommand, conn); SqlCommand.Parameters.AddWithValue("@strval", SearchTextBox.Text); 

Answer 8

Finally, you will need to extract data from your database and display it on the screen or print it in reports. Manipulating double quotes or any additional character can be very confusing.

By converting your strings to HTML before INSERT or UPDATE, you avoid any confusion about managing quotes. In SELECT, time will be easy to convert from HTML. During reporting time (since reporting tools (such as Crystal Reports) offer an HTML formatting option), you don’t even have to do anything to display the data in the right way.

By the way, don't forget to hang the guy who invented this company name.