Java Preparedstatement error when reading from MySql Database

Question

I'm working on a simple application that pulls data from a local database. The below code works fine when I use a string for the SQL query, but I can not get it to work with PreparedStatement. I have reviewed similar problems posted here but most of those were caused by doing this, preparedStmt.executeQuery(query); instead of this preparedStmt.executeQuery(); Here is the code,

    private final String POSTTITLE=     "posttitle"; // DB Column name
    private final String POSTCONTENT=   "content"; // DB Column name

   public String getDbContent(){
        try{
            String query ="select values(?, ?) from blog";
            PreparedStatement preparedStmt = this.connect.prepareStatement(query);
            preparedStmt.setString (1,POSTTITLE);
            preparedStmt.setString (2,POSTCONTENT);
            ResultSet rs = preparedStmt.executeQuery();
            rs.next();
            return(rs.getString(this.POSTCONTENT)); //Will replace with loop to get all content
        } catch(Exception e) {
           System.err.println("Error Reading database!");
           System.err.println(e);
           return("Error: "+e);
        }
    }

This is the error I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''posttitle', 'content') from blog' at line 1

Are you sure you wanted to have the column names separated from the query? The syntax for SELECT is never SELECT VALUES() FROM ... but rather SELECT expression1, expression2, ... FROM ... with the expressions involving column names. In most cases, you know the columns you want to get in advance. It's not clear what you are trying to achieve with this query. — RealSkeptic
– RealSkeptic, Commented Apr 4, 2017 at 15:16
Yes I think you're right, there is no real reason to programmatically input the columns. Thank you. — JHRS
– JHRS, Commented Apr 5, 2017 at 17:03

Jon Skeet · Accepted Answer · 2017-04-04 15:11:45Z

2

Parameters in prepared statements are for values - you're trying to use them to select fields. They just don't work that way.

In this very specific instance, you'll need to make the SQL dynamic. However, you'll want to make sure that whatever code you have to allow your columns to be specified is tightly constrained to avoid SQL injection attacks. (For example, you could have an enum with the columns in, or a whitelist of allowed values.)

answered Apr 4, 2017 at 15:11

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

Comments

Beri · Accepted Answer · 2017-04-04 15:12:53Z

0

Try concatenating select query:

String query ="select "+POSTTITLE+","+POSTCONTENT+" from blog";

Remember that prepared statements are for values, not query parameters, for them we use simply concatenations.

answered Apr 4, 2017 at 15:12

Beri

11.6k4 gold badges38 silver badges59 bronze badges

Comments

ipper · Accepted Answer · 2017-04-04 15:15:48Z

0

Try this:

String query ="select POSTTITLE, POSTCONTENT from blog";
PreparedStatement preparedStmt = this.connect.prepareStatement(query);
ResultSet rs = preparedStmt.executeQuery();
rs.next();

There is no need to use field names as parameter.

answered Apr 4, 2017 at 15:15

ipper

7844 silver badges13 bronze badges

Collectives™ on Stack Overflow

Java Preparedstatement error when reading from MySql Database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related