MS SQL Exception

Question

I try to build a Insert Query with ? parameters, however my console is showing an error when I try to execute the query because the column names are wrong. However the table has exactly the follwoing column names. Here my code:

String query = "INSERT INTO " + table +" ( ? ) VALUES ( ? ) ;"; PreparedStatement pstmt = null;

            ArrayList<String> listWithQuotesFields = new ArrayList<String>();

            for(String element : fields){
                listWithQuotesFields.add(element);
            }
            String finalStringFields = StringUtils.join(listWithQuotesFields.stream().toList(),",");
            log.error(finalStringFields);

            ArrayList<String> listWithQuotesValues = new ArrayList<String>();

            for(String element : values){
                listWithQuotesValues.add("'"+element+"'");
            }
            String finalStringValues = StringUtils.join( listWithQuotesValues.stream().toList(),",");
            log.error(finalStringValues);
            pstmt = getConnection().prepareStatement(query);
       

            pstmt.setString(1, finalStringFields);
            pstmt.setString(2, finalStringValues);


            log.error("Prepared Statement:" + pstmt);


            ResultSet rs = pstmt.executeQuery();

Errors: 2022-05-11 12:11:40.827 [main] ERROR Database.InsertQuery - Username,Email,Password 2022-05-11 12:11:40.829 [main] ERROR Database.InsertQuery - 'testInjection','test','test' Connected Driver name: Microsoft JDBC Driver 8.2 for SQL Server Driver version: 8.2.1.0 Product name: Microsoft SQL Server Product version: 15.00.2080 2022-05-11 12:11:41.886 [main] ERROR Database.InsertQuery - Prepared Statement:SQLServerPreparedStatement:1 com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name '@P0'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444) at Database.InsertQuery.BuildQuery(InsertQuery.java:116)

score 0 · Answer 1 · answered May 11 '22 at 10:07

0

It looks like the problem is using the table name and field names as variables. If it is single quoted then it will raise an error.

create table testInjection(test varchar(10));
GO

✓

insert into testInjection (test) values ('test' );
GO

1 rows affected

insert into 'testInjection' ('test') values ('test' );
GO

Msg 102 Level 15 State 1 Line 1

Incorrect syntax near 'testInjection'.

db<>fiddle here

answered May 11 '22 at 10:07

I would like my Query to be " Insert Into UserLogin (Username, Email, Password) VALUES ('testInjection','test','test'). I changed my query to String query = "INSERT INTO " + table +" ( ? ) VALUES ( ? ) ;"; . Now the table is at least recognized. However the colum names are invalid. If I log the String of TAble columns it says: Username,Email,Password . Which are the correct column names. The values I want to add are:'testInjection','test','test'.Those are also saved in the String finalStringValues. Just from the looks the query should be correct however it is not working... – Kiki Beh May 11 '22 at 10:20
The column names should be unquoted, or possible double quoted, but not single quoted as the are identifiers and not values. – May 11 '22 at 10:32
They are unquoted the String of the Columnnames hold the value "Username,Email,Password" – Kiki Beh May 11 '22 at 10:39
I understand but if the string is quoted it will become `Insert Into UserLogin ('Username, Email, Password') VALUES ('testInjection','test','test').` – May 11 '22 at 10:58
Ok Thanks and how can I prevent that ' are added within the ()? – Kiki Beh May 11 '22 at 11:10
You have to use some sort of dynamic sql but, whatever you do don't use user intput directly in dynamic sql! see https://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-input-parameter-in-sql-stored-procedure – May 11 '22 at 12:10
see also https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter – May 11 '22 at 12:11

MS SQL Exception

1 Answers1