-1

If I need to use a prepared statement, what do I do?

Statement statement = conn.createStatement();
String sql ="SELECT * FROM polls WHERE pollname LIKE '%"+search+"%' or side1 LIKE '%"+search+"%' or side2 LIKE '%"+search+"%' ORDER BY totalvotes DESC";

ResultSet resultSet = statement.executeQuery(sql);
if(resultSet.next() == false){
user207421
  • 305,947
  • 44
  • 307
  • 483
cbankr22
  • 1
  • 1
    What part of using prepared statements do you need help with? What have you tried? – Scott Hunter Nov 03 '19 at 01:12
  • 2
    You'll need to replace `'%"+search+"%'` with `?` and bind your parameters. This thing is full of holes. – tadman Nov 03 '19 at 01:40
  • Not to mention that _using Servlets directly at all_ is usually a bad sign; frameworks like Spring MVC can eliminate nearly all of this low-level code for you. – chrylis -cautiouslyoptimistic- Nov 03 '19 at 02:09
  • @tadman after adding the prepared statement, are there any more holes? – cbankr22 Nov 03 '19 at 02:12
  • @chrylis-onstrike- i accidentally typed it wrong. this code is actually in a jsp file that displays the data in an html table. – cbankr22 Nov 03 '19 at 02:13
  • [Even worse.](https://stackoverflow.com/questions/38340291/jsp-what-is-wrong-with-scriptlets-and-what-to-use-instead) (Spring MVC+Thymeleaf+Spring Boot+Spring Data JPA is my recommendation if you have options.) – chrylis -cautiouslyoptimistic- Nov 03 '19 at 02:15
  • other than the fact that it may be hard to read, is there really anything wrong with jsps and servlets? i have written over 1,500 lines of code for this project and it would be a shame if I had to rewrite anything. @chrylis-onstrike- – cbankr22 Nov 03 '19 at 02:19
  • With JSP? It's difficult to test, but that's the main downside. Scriptlets? All the downsides in the link. Servlets? Nothing _wrong_ with it, per se, but your 1500 lines of code could probably be reduced to about 250–300. – chrylis -cautiouslyoptimistic- Nov 03 '19 at 02:23
  • around half of the code is written in servlets, with the other half being jsp's, html, or css. I am very new to programming. You can check out my github project if you would like to take a look at poorly written code [link](https://github.com/cbankr22/PollMe) thanks for everything though. – cbankr22 Nov 03 '19 at 02:27

1 Answers1

1

With PreparedStatement, use ? to parameter placeholder, then use setXXX to set the parameter value across the parameter index(parameter Index started from 1, not 0).

Below the code:

PreparedStatement preparedStatement = conn.prepareStatement(
            "SELECT * FROM polls WHERE pollname LIKE ? or side1 LIKE ? or side2 LIKE ? ORDER BY totalvotes DESC");

String searchWizard = "%" + search + "%";
preparedStatement.setString(1, searchWizard);
preparedStatement.setString(2, searchWizard);
preparedStatement.setString(3, searchWizard);
ResultSet resultSet = preparedStatement.executeQuery();
Hong Tang
  • 2,334
  • 1
  • 6
  • 7
  • @ColinAcker To be clear: using a prepared statement automatically eliminates the threat of SQL injection attacks. If you have any kind of input from outside your own code (input from users, inputs from imported data, inputs from other libraries or services), always use a prepared statement. Use a simple statement only with inputs generated by your own code, such as the current date. – Basil Bourque Nov 03 '19 at 04:55