java.sql.SQLException: Wrong number of parameters error

Question

I am trying to insert data into a table in SQL server through java. The first is an ID, the second is a nvarchar name, and the third is an xml type (Where I insert it as a string).

I have a db manager class that manages the query execution itself (Including the connection to the DB).

The code for inserting :

stmt = String.format("INSERT INTO [dbo].[WorkflowFull] ([ID], [Name], [Workflow]) VALUES (%d, N'%s', N'%s')", workflowID, fileNameForDB, fileContent);
        try{
            dbManager.insert(stmt);
        } catch (SQLException e){
            System.out.println("Problem adding workflow " + fileName + " to DB");
            e.printStackTrace();
            continue;
        }

The error I am getting:

java.sql.SQLException: Wrong number of parameters: expected 3, was given 0 Query: INSERT INTO [dbo].[WorkflowFull] ([ID], [Name], [Workflow]) VALUES (1, N'testWorkflow', N'<Workflow name="NetMonitorTester" start="atomic">
<atomic name="atomic" startingPoint="monitor" hostType="PC" multiplicity="1">
    <activity package="MonitorNetwork" name="monitor" input="null" stopCondition="never">
        <filter query="regex:GET.+(.{10,})\1{10,}"/>
        <resultQuery retrieve="SELECT * FROM PUBLIC.TCP_PACKETS"/>
    </activity>
</atomic>
</Workflow>') Parameters: []
at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)
at org.apache.commons.dbutils.QueryRunner.insert(QueryRunner.java:610)
at org.apache.commons.dbutils.QueryRunner.insert(QueryRunner.java:516)
at org.bgu.ddms.utils.dbutils.DbManager.insert(DbManager.java:165)
at org.bgu.ddms.cnc.CNC.populateWorkflows(CNC.java:289)
at org.bgu.ddms.cnc.CNC.populateDB(CNC.java:244)
at org.bgu.ddms.cnc.Main.main(Main.java:108)

When I run the exact same query in SSMS, everything works fine

Side note: I'm not an expert on the framework you're using (`[dbo]` etc. don't look like good old sql) but `String.format(...)` looks a lot like being vulnerable to SQL injection. You should _never_ directly concatenate potentially untrusted values into a query. Since you used the JDBC tag I'd suggest using `PreparedStatement` instead (or however your framework exposes that). — Thomas, Mar 16 '17 at 08:26
In conjunction to my comment above I'd suspect that feeding unescaped XML, especially since it contains a query itself, directly into a query string is the problem you're having. That again would probably be solved by using a `PreparedStatement` where you call `setParameter(3, yourXmlString)` instead of concatenating it into the query. — Thomas, Mar 16 '17 at 08:29
YCF_L, the N before the string is to say that it is a nvarchar (Unicode) instead of just a varchar, so the N before the quotes is okay. — Zionsof, Mar 16 '17 at 08:40

Youcef LAIDANI · Accepted Answer · 2017-09-14T19:11:28.257

2

The first is an ID, the second is a nvarchar name, and the third is an xml type (Where I insert it as a string).

Your way can cause a syntax error like you have now, the bad thing is SQL Injection so to avoid this you have to use PreparedStatement instead, for example :

String query = "INSERT INTO [dbo].[WorkflowFull] ([ID], [Name], [Workflow]) VALUES (?, ?, ?)");
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, workflowID);
preparedStatement.setString(2, fileNameForDB);
preparedStatement.setString(3, fileContent);

edited Sep 14 '17 at 19:11

answered Mar 16 '17 at 08:32

Youcef LAIDANI

55,661
15
90
140

I would put more interest on the `PreparedStatement` since this would correct the problem (the setXXX method would escape the value) and also be safer. – AxelH Mar 16 '17 at 08:38
what did you mean @AxelH i already put an example how to work with PreparedStetement? do you want me to change `setInt` to `setXXX` or what? – Youcef LAIDANI Mar 16 '17 at 08:41
1

Well, I have commented during your edit to add the example, so this was exactly what I meant ;) – AxelH Mar 16 '17 at 08:43
Switching to prepared statement (Even though it kinda overrides the current management of the connection) fixed it. Thanks! – Zionsof Mar 16 '17 at 09:12
`N'...'` denotes an NVARCHAR literal, see http://stackoverflow.com/questions/19325232/sql-server-t-sql-n-prefix-on-string-literal – Mark Rotteveel Mar 16 '17 at 13:38
i see, so the query should look like this `...VALUES (?, N?, N?)` @MarkRotteveel i'm wrong? – Youcef LAIDANI Mar 16 '17 at 13:41
`N'..'` is a literal (like `'..'`, or `x'..'`), when using parameters you don't need them (it would even be a syntax error), as there the parameter type is inferred. – Mark Rotteveel Mar 16 '17 at 14:03
ok thank you @MarkRotteveel so should i remove the first part of my answer? i think it is not correct? – Youcef LAIDANI Mar 16 '17 at 14:12

java.sql.SQLException: Wrong number of parameters error

1 Answers1