0

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:

SELECT 1 FROM OPENQUERY('LINK_SERVER1', 'SELECT 1 FROM TABLE_ABC');

In my Java class I am using PreparedStatement as below to execute the above OPENQUERY as below:

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();

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.

I attempted to change the above code to use setString on PreparedStatement as below.

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();

However the above code does not work as I expect it to. At run time I get below exception on call to ps.executeQuery():

java.sql.SQLException: Incorrect syntax near '@P0'.

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.

Has anyone come across this issue and resolved it? Any pointers towards the resolution of this is appreciated.

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)
NITIN SHUKLA
  • 11
  • 5
  • Did you try it with `ps.setString(0, linkServerName)` ? – alzaimar Feb 09 '15 at 06:35
  • @alzaimar: Parameters are 1 based index, hence ps.setString(0, linkServer) throws exception - "Invalid parameter index 0" – NITIN SHUKLA Feb 09 '15 at 06:50
  • Post your full stacktrace .Something wrong here `String remoteQuery = "'SELECT 1 FROM TABLE_ABC'";` You dont need single qoutes again.. – Rookie007 Feb 09 '15 at 08:05
  • @looser: I think remoteQuery requires the single quote since the code prior to this change needed one (second code snippet above) . Having said that I tried removing the single quotes in remoteQuery but the result is same. I will add the stacktrace in my post. – NITIN SHUKLA Feb 09 '15 at 08:43
  • @NITINSHUKLA I think that `LINK_SERVER1` is an dientifier syntax check the syntax here.. https://msdn.microsoft.com/en-us/library/ms188427.aspx May be try using `ps.setURL()`.. – Rookie007 Feb 09 '15 at 09:38
  • @looser LINK_SERVER1 is the name of remote linked server and does not seem to be URL. However I tried ps.setURL() but no luck yet. – NITIN SHUKLA Feb 11 '15 at 07:20

0 Answers0