title: How to set parameters of Prepared Statement query that uses OPENQUERY to avoid SQL Injection
body: <p>In my java application I am using OPENQUERY to execute an SQL query in remote linked MSSQL server and fetch result. Below is an example of OPENQUERY I am using:</p>
  
  <pre><code>SELECT 1 FROM OPENQUERY('LINK_SERVER1', 'SELECT 1 FROM TABLE_ABC');
  </code></pre>
  
  <p>In my Java class I am using PreparedStatement as below to execute the above OPENQUERY as below:</p>
  
  <pre><code>String linkServerName = "LINK_SERVER1";
  String remoteQuery = "'SELECT 1 FROM TABLE_ABC'";
  String openQuery = "SELECT 1 FROM OPENQUERY(" + linkServerName + ", " + remoteQuery + ")";
  PreparedStatment ps = connection.prepareStatement(openQuery);
  ps.executeQuery();
  </code></pre>
  
  <p>The above code works as expected. However the problem is that it is liable for SQL Injection and HP Fortify reports it as SQL Injection vulnerable.</p>
  
  <p>I attempted to change the above code to use setString on PreparedStatement as below.</p>
  
  <pre><code>String linkServerName = "LINK_SERVER1";
  String remoteQuery = "'SELECT 1 FROM TABLE_ABC'";
  String openQuery = "SELECT 1 FROM OPENQUERY(?, ?)";
  PreparedStatment ps = connection.prepareStatement(openQuery);
  ps.setString(1, linkServerName);
  ps.setString(2, remoteQuery);
  ps.executeQuery();
  </code></pre>
  
  <p>However the above code does not work as I expect it to. At run time I get below exception on call to ps.executeQuery():</p>
  
  <pre><code>java.sql.SQLException: Incorrect syntax near '@P0'.
  </code></pre>
  
  <p>I am not clear what is incorrect with above code. It seems that the MSSQL jdbc driver isn't liking it, and call to setString method on PreparedStatement did not set the parameter correctly. </p>
  
  <p>Has anyone come across this issue and resolved it? Any pointers towards the resolution of this is appreciated.</p>
  
  <pre><code>java.sql.SQLException: Incorrect syntax near '@P0'.
              at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
              at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
              at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
              at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
              at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
              at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
              at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
              at com.aviseurope.rm.fcst.modules.service.HealthCheckServiceImpl.canConnectToBiSsde(HealthCheckServiceImpl.java:658)
  </code></pre>