title: How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?
body: <p>To start this off, I am well aware that parameterized queries are the best option, but I am asking what makes the strategy I present below vulnerable. People insist the below solution doesn't work, so I am look for an example of why it wouldn't.</p>
  
  <p>If dynamic SQL is built in code using the following escaping before being sent to a SQL Server, what kind of injection can defeat this?</p>
  
  <pre><code>string userInput= "N'" + userInput.Replace("'", "''") + "'"
  </code></pre>
  
  <p>A similar question was answered <a href="https://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use">here</a>, but I don't believe any of the answers are applicable here.</p>
  
  <p>Escaping the single quote with a "\" isn't possible in SQL Server.</p>
  
  <p>I believe <strong>SQL Smuggling</strong> with Unicode (outlined <a href="https://www.owasp.org/images/d/d4/OWASP_IL_2007_SQL_Smuggling.pdf" rel="noreferrer">here</a>) would be thwarted by the fact that the string being produced is marked as Unicode by the N preceding the single quote. As far as I know, there are no other character sets that SQL Server would automatically translate to a single quote. Without an unescaped single quote, I don't believe injection is possible.</p>
  
  <p>I don't believe <strong>String Truncation</strong> is a viable vector either. SQL Server certainly won't be doing the truncating since the max size for an <code>nvarchar</code> is 2GB <a href="http://msdn.microsoft.com/en-us/library/ms186939.aspx" rel="noreferrer">according to microsoft</a>. A 2 GB string is unfeasible in most situations, and impossible in mine.</p>
  
  <p><strong>Second Order Injection</strong> could be possible, but is it possible if:</p>
  
  <ol>
  <li>All data going into the database is sanitized using the above method</li>
  <li>Values from the database are never appended into dynamic SQL (why would you ever do that anyways, when you can just reference the table value in the static part of any dynamic SQL string?).</li>
  </ol>
  
  <p>I'm not suggesting that this is better than or an alternative to using parameterized queries, but I want to know how what I outlined is vulnerable. Any ideas?</p>