title: Is CallableStatement really immune to SQL injection?
body: <p>We have a Java application that communicates with multiple SQL Server databases on the same 
  box.  The number of and names of these databases vary.  By and large, we use almost exclusively stored procedures with CallableStatement to access the databases.  We are extremely good about avoiding SQL injection and using bind variables.</p>
  
  <p>The only area of concern is that the database name itself is concatenated into the SQL that we pass to the CallableStatement as such:</p>
  
  <pre><code>"{call [" + dbName + ".dbo." + procName + "(?, ?, ?)}"
  </code></pre>
  
  <p>procName is hard-coded into child classes using a template method pattern, so that string is guaranteed safe.</p>
  
  <p>dbName is defined externally.  I have tried setting dbName to all sorts of patterns to escape the syntax and exploit this on my development environment and have been unsuccessful.</p>
  
  <p>I have set it to the following to produce the following SQL calls (table and proc names changed to protect the innocent):</p>
  
  <pre><code>securitytest].nx_proc()};delete from poor_victim_table;
  </code></pre>
  
  <p>becomes</p>
  
  <pre><code>{call [securitytest].nx_proc()};delete from poor_victim_table;].dbo.proper_proc_name()}
  </code></pre>
  
  <p>and</p>
  
  <pre><code>securitytest].nx_proc()};exec('delete from poor_victim_table');
  </code></pre>
  
  <p>becomes</p>
  
  <pre><code>{call [securitytest].nx_proc()};exec('delete from poor_victim_table');].dbo.proper_proc_name(?,?,?,?,?,?,?)}
  </code></pre>
  
  <p>Results in <code>Incorrect syntax near ')'.</code> and <code>poor_victim_table</code> still has rows.  I have used <code>truncate table</code>, <code>drop table</code> and <code>drop database</code> and when they didn't work, I switched to simple <code>delete</code> to rule out security settings.</p>
  
  <p>If I use a proc that takes bind parameters, I always get a mismatch between the number of expected parameters and supplied parameters such as <code>The index 1 is out of range.</code>.</p>
  
  <pre><code>securitytest]};exec('delete from poor_victim_table');
  </code></pre>
  
  <p>becomes</p>
  
  <pre><code>{call [securitytest]};exec('delete from poor_victim_table');].dbo.proper_proc_name(?,?,?,?,?,?,?)}
  </code></pre>
  
  <p>All roads seem to lead to a runtime error and the SQL does not execute.  Of course, this is great.  But I want to make sure it's failing because it <strong>cannot</strong> succeed and not failing because I am failing to try the right combination.</p>
  
  <p>The popular opinion / urban myth is that using a stored procedure makes you immune to SQL injection, but I prefer to not trust absolute statements like that when it comes to security.</p>
  
  <p>After researching this for a while, the best I came up with is this stackoverflow question: <a href="https://stackoverflow.com/questions/1119656/sql-injection-no-danger-on-stored-procedure-call-on-iseries">SQL injection - no danger on stored procedure call (on iSeries)?</a>.  It seems to support using CallableStatement because it protects you from SQL injection <strong>unless your proc code itself makes dynamic SQL out of an input parameter</strong>.</p>
  
  <p>So, my question to the community is, assuming the SQL code in a proc is safe, does using CallableStatement in JDBC really prevent SQL injection?  Or does the SQL Server driver parse the string in a way that prevents it, but other drivers may not?  Or am I not trying hard enough?</p>
  
  <p>If it is safe, how is that guarantee made?  Is it due to the abstract syntax of using <code>{ call blah(?) }</code> which is not real SQL, but gets translated to SQL?</p>