-4

In this one stored procedure being called by an API to update a record, there's lots of instances of this:

SET @NewField = REPLACE(ISNULL(@InputField, ''), '''', '''''')

I get the ISNULL part, and I know '' escapes a single quote, but I cannot figure out what the intent of four and six quotes is?

redOctober13
  • 3,662
  • 6
  • 34
  • 61
  • 2
    Have you tried selecting that expression for some test values having multiple quote characters? – HoneyBadger Sep 08 '22 at 19:47
  • 2
    One quote to start the string literal, one quote to escape the next one, one quote as the content of the string, one quote to end the string literal. That makes four quotes for one quote you are looking for in the string. Six quotes hence are two quotes as the content of the string literal. That makes: replace each quote with two quotes. – Thorsten Kettner Sep 08 '22 at 19:50
  • 1
    Excessive quote nesting like this can be made a whole lot more readable through judicious use of `CHAR(39)`, which is also a single quote, but without requiring all the doubling up. – Jeroen Mostert Sep 08 '22 at 20:09

1 Answers1

1

A literal quote in a string literal needs to be escaped by doubling it. So the (interpreted, runtime) value of '''' is just one single quote. Replacing what looks like four quotes by what looks like six quotes is t-sql syntax to mean:

Search the string for occurrences of a single quote, and replace each with two consecutive single quotes.

Thus SQL's syntax is weird sometimes would become SQL''s syntax is weird sometimes.

One application of this I've seen is to prepare a string for subsequent literal execution (with the "execute" command). This is a sensitive area of SQL programming because it deals with security: Correctly escaping single quotes is one building block of safe-for-scripting queries.

Cee McSharpface
  • 8,493
  • 3
  • 36
  • 77
  • I think that's probably it, because the value is later used to build a SQL query as a string (which I hate having to read). So if the input value was a name like `D'Angelo`, it would become `D''Angelo` so that the quote would be preserved, does that sound right? Although in that later query, each is added to the query like this: `@FIRSTNAME+''','''+@MIDDLENAME+''','''+`... I wish there was better syntax for things like this. – redOctober13 Sep 08 '22 at 20:11
  • 1
    @redOctober13 there is a better syntax (in my opinion). You can use QUOTENAME with the second parameter. QUOTENAME(column, CHAR(39)) will quote the string and double-up embedded single quotes if needed. Also, I would recommend looking up CONCAT and using that function to concatenate strings. – Jeff Sep 08 '22 at 21:57
  • Thanks for those tips. One thing I think would be good is making a stored procedure for the INSERT statement instead of doing this big string concatenation and executing that string. – redOctober13 Sep 09 '22 at 00:27