PreparedStatement setString doesn't work, there are still question marks

Question

i am currently working on a Project with a database in the back and i wanted to order the files by coloums with this method: For this reason there are 4 different parameter in the method head, the first one is for the connection, the next one is the parameter is the username, because only the person who uploads the file can see the file and the next one is the coloum of the table in the database and the next one is ASC or DESC.

public ArrayList<Daten> meineDaten(Connection conn,String sortierparameter,String spalte,String reihung)
    {
        //generieren einer ArrayList zum Zwischenspeichern von den Werten aus der Datenbank
        ArrayList<Daten> DatenSortiertPrivate = new ArrayList<>();
        String READ_DATEN_PRIVATE = null;

        //SQL-Abfrage
        if(reihung.equals("ASC"))
        {
            READ_DATEN_PRIVATE="select uploadid,dateityp, dateiname, autor, uploaddatum, dokumentdatum, status from uploaddaten where uploader= ? and zustand='true' order by ? ASC;";
        }
        else if(reihung.equals("DESC")){
            READ_DATEN_PRIVATE="select uploadid,dateityp, dateiname, autor, uploaddatum, dokumentdatum, status from uploaddaten where uploader= ? and zustand='true' order by ? DESC;";
        }

        //READ_DATEN_PRIVATE="select uploadid,dateityp, dateiname, autor, uploaddatum, dokumentdatum, status from uploaddaten where uploader=? and zustand='true' order by ? ?;";

        try {
            pstmt = conn.prepareStatement(READ_DATEN_PRIVATE);
            pstmt.setString(1, sortierparameter);
            pstmt.setString(2, spalte);
            rs = pstmt.executeQuery();
            System.out.println("SQL: "+READ_DATEN_PRIVATE);
            while(rs.next())
            {
                int uploadid = rs.getInt(1);
                String dateityp = rs.getString(2);
                String dateiname = rs.getString(3);
                String autor = rs.getString(4);
                String uploaddatum = rs.getString(5);
                String dokumentdatum = rs.getString(6);
                String status = rs.getString(7);

                Daten zeile = new Daten(uploadid,dateityp,dateiname, autor, uploaddatum, dokumentdatum, status);
                DatenSortiertPrivate.add(zeile);
            }

            pstmt.close(); pstmt=null;
            rs.close();rs=null;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return DatenSortiertPrivate;

    }

And i don't know why this is the result: SQL Daten auf Website angebenselect uploadid,dateityp, dateiname, autor, uploaddatum, dokumentdatum, status from uploaddaten where uploader=? and zustand='true' order by ? ASC;

For example to order by "dateiname" and the username is thoker and ASC.

This method will be used by clicking on a button.

P.S. Sorry for my bad english

You are printing the contents of the variable `READ_DATEN_PRIVATE` - why do you think that will change? — , Mar 29 '18 at 11:08
The short answer to your question "how do I get the SQL statement with the values inserted into the string?" is "you can't". Why do you need to do this? — Luke Woodward, Mar 29 '18 at 20:34

Jay Shankar Gupta · Answer 1 · 2018-03-29T07:14:53.133

1

You are printing READ_DATEN_PRIVATE. Print pstmt after prepareStatement then you can check updated query

 System.out.println("SQL Daten auf Website angeben Before"+READ_DATEN_PRIVATE);
        try {
            pstmt = conn.prepareStatement(READ_DATEN_PRIVATE);
            pstmt.setString(1, sortierparameter);
            pstmt.setString(2, spalte);
            rs = pstmt.executeQuery();
            System.out.println("After Change:" + pstmt);

edited Mar 29 '18 at 07:14

answered Mar 29 '18 at 06:47

Jay Shankar Gupta

5,918
1
10
27

SQL Daten auf Website angebenselect uploadid,dateityp, dateiname, autor, uploaddatum, dokumentdatum, status from uploaddaten where uploader= ? and zustand='true' order by ? ASC; After: select uploadid,dateityp, dateiname, autor, uploaddatum, dokumentdatum, status from uploaddaten where uploader= ? and zustand='true' order by ? ASC; i tried it as you said but it is still not working – HTLgurl Mar 29 '18 at 09:32
Is there anything wrong with the PreparedStatement or the method setString – HTLgurl Mar 29 '18 at 09:39

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Long answer short:

You can't replace values within order by ? in both of your queries.

Reason:

Placeholders ? can be applied for parameters of column values, but not to

column or table names,
sort columns or sort order directions (your case) or to
SQL functions/clauses.

So to fix the main problem, exchange order by ? with "... order by " + sortierparameter + ".... Yet, this value should be carefully checked to avoid errors at runtime. Better define allowed sort order parameters by means of an Enum.

See Oracle's tutorial on Using Prepared Statements for reference.

Side note:

You should respect the order of parameter placeholders in your code:

pstmt.setString(1, sortierparameter);
pstmt.setString(2, spalte);

is wrong, given the semantics (and German translation of "sortierparameter"). You are incorrectly setting pstmt.setString(2, spalte); to the 2nd parameter.

I think it must read as follows pstmt.setString(1, spalte); assuming you want to set the value of uploader= ? with it and given that you fix the aforementioned main problem of your approach.

Hope it helps.

PreparedStatement setString doesn't work, there are still question marks

2 Answers2

Reason:

Side note: