I am getting SQL errors in my console and I tried to do the query without the question marks and it worked fine. However as we all know that is very bad practice and leaves me open to injection.
Take these methods below for example. They all look correct to me, they all were working before I put the question marks in the query and all the reading I have done seems to reflect I am doing the queries correctly.
public void insert(User user) {
id = user.getId();
name = user.getName();
text = user.getText();
email = user.getEmail();
String query = "INSERT INTO offers (name, email, text) VALUES (?, ?, ?)";
System.out.println(user.getName()+" "+user.getEmail()+" "+user.getText());
try {
System.out.println(query);
ps = conn.prepareStatement(query);
ps.setString(1, name);
ps.setString(2, email);
ps.setString(3, text);
ps.executeUpdate(query);
} catch (SQLException e) {
System.out.println("There was an error in insert: " + e);
e.printStackTrace();
}
}
public void delete(User user) {
name = user.getName();
email = user.getEmail();
String query = "DELETE FROM offers WHERE name= ? AND email=?";
System.out.println(query);
try {
ps = conn.prepareStatement(query);
ps.setString(1, name);
ps.setString(2, email);
ps.executeUpdate(query);
} catch (SQLException e) {
System.out.println("Error while trying to delete "+e);
e.printStackTrace();
}
}
public void update(User user) {
id = user.getId();
name = user.getName();
text = user.getText();
email = user.getEmail();
String query = "UPDATE offers SET name=? , text=? WHERE email= ?";
try {
ps = conn.prepareStatement(query);
ps.setString(1, user.getName());
ps.setString(2, user.getEmail());
ps.setString(3,user.getText());
ps.executeUpdate(query);
} catch (Exception e) {
System.out.println("There was an error in update: " + e);
}
}
public List<User> returnDatabase() {
//will have this method return a list soon
List<User> users = new LinkedList<User>();
User user = null;
String query = "SELECT * FROM offers";
try {
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery(query);
while(rs.next()){
id = rs.getInt("id");
name = rs.getString("name");
email = rs.getString("email");
text = rs.getString("text");
user.setId(id);
user.setName(name);
user.setEmail(email);
user.setText(text);
users.add(user);
System.out.println(id+" "+name+" "+email+" "+text);
}
} catch (Exception e) {
System.out.println("could not return database "+e);
}
return users;
}
public String getSucessful() {
return sucessful;
}
}
console
Connected
Fire constructor
Fired
asdf asdf asdf
INSERT INTO offers (name, email, text) VALUES (?, ?, ?)
There was an error in insert: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '?, ?, ?)' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '?, ?, ?)' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1549)
at com.practice.database.MySQLdatabase.insert(MySQLdatabase.java:44)
at com.practice.controller.DatabaseController$Actions.actionPerformed(DatabaseController.java:89)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2346)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6525)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3322)
at java.awt.Component.processEvent(Component.java:6290)
at java.awt.Container.processEvent(Container.java:2234)
at java.awt.Component.dispatchEventImpl(Component.java:4881)
at java.awt.Container.dispatchEventImpl(Container.java:2292)
at java.awt.Component.dispatchEvent(Component.java:4703)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4898)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4533)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4462)
at java.awt.Container.dispatchEventImpl(Container.java:2278)
at java.awt.Window.dispatchEventImpl(Window.java:2739)
at java.awt.Component.dispatchEvent(Component.java:4703)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:751)
at java.awt.EventQueue.access$500(EventQueue.java:97)
at java.awt.EventQueue$3.run(EventQueue.java:702)
at java.awt.EventQueue$3.run(EventQueue.java:696)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:86)
at java.awt.EventQueue$4.run(EventQueue.java:724)
at java.awt.EventQueue$4.run(EventQueue.java:722)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:721)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
