Strange SQL statements being inserted into database

[mithesh raj]

I have a problem with my Java application. The application is based on users who have to enter their username (their email address) so as to fill in an input form. The functionality of the input form is that the user has to enter for book names in order of priority. In the application, suppose a user's email address is "[e-mail]"....he will have to input only "david.ferno" in the JTextBox...and a SQL LIKE statement is used check the corresponding User ID from the "bookuser" table and store it into the "priority" table along with the book priority choices.

I have used PreparedStatement for the SQL retrieval from database but when values are being inserted to the , instead of saving User ID like "user88", it is saving the SQL statement itself inside the database. What mean to say, in the "priority" table, instead of saving user88 (as an example)....it is saving "SELECT User_ID FROM user WHERE email_address"

Here are my codes>>

try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

                Connection con = DriverManager.getConnection(database ,"","");  //username &password not being used

                Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); //The Result set can now scroll forward and back




            String squery = "SELECT User_ID FROM bookuser WHERE email_address = ? ";
            PreparedStatement pstmt = con.prepareStatement(squery);
            pstmt.setString (1, txt_mail.getText() + '*');
                            pstmt.executeQuery();

            String sql = "INSERT INTO priority VALUES('" 

                + squery + "','"
                + txt_book1.getText() + "','"
                + txt_book2.getText() + "','" 
                + txt_book3.getText() + "','"
                + txt_book4.getText()+ "' )";

[Jelle Klap]

If the idea is to make the SELECT query a subquery in the INSERT query, that's not going to work this way. What you'd need to do is create a PreparedStatement for the entire insert SQL String, which must include the select subquery, and instead of concatenating user input directly into that query String, use parameters like you do in the current select query String (the ? notation).

[chris webster]

In your "sql" string, you are inserting the value of your original "SELECT" statement (squery) into the first column of the table called "priority". As Jelle says, you need to write your SQL properly - and test it in your database's SQL shell before you throw it into a Java program, so you can be sure your SQL works.