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
– Thomas, Commented Mar 16, 2017 at 8: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
– Thomas, Commented Mar 16, 2017 at 8: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
– Zionsof, Commented Mar 16, 2017 at 8:40

Youcef LAIDANI · Accepted Answer · 2017-09-14 19:11:28Z

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, 2017 at 19:11

answered Mar 16, 2017 at 8:32

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

AxelH Over a year ago

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.

Youcef LAIDANI Over a year ago

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?

AxelH Over a year ago

Well, I have commented during your edit to add the example, so this was exactly what I meant ;)

Zionsof Over a year ago

Switching to prepared statement (Even though it kinda overrides the current management of the connection) fixed it. Thanks!

Mark Rotteveel Over a year ago

N'...' denotes an NVARCHAR literal, see stackoverflow.com/questions/19325232/…

|

Collectives™ on Stack Overflow

java.sql.SQLException: Wrong number of parameters error

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related