What's wrong with this SQL syntax?

Question

I am trying to build registration system with java servlet. And insert data into a mySQL database. But I get a syntax error. I just finished reading Wiley mySQL and Java developers guide book.

And I am kinda new to servlet programming, so if there is easy way to do things I do please tell me.

 package com.app.base;

 import java.io.IOException;
 import java.io.PrintWriter;
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.Statement;

 import javax.servlet.RequestDispatcher;
 import javax.servlet.ServletException;
 import javax.servlet.http.HttpServlet;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;

 import com.app.pojo.*;

 public class RegisterServlet extends HttpServlet{

MySqlDB mysql;

@Override
public void init() throws ServletException {
    // TODO Auto-generated method stub
    mysql = new MySqlDB();

}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
        throws ServletException, IOException {

    PrintWriter out = null;
    //Connection connection = null;
    //Statement statement;
    //ResultSet rs;

    resp.setContentType("text/html");
    out = resp.getWriter();


    try{
        mysql.createConnection();
    }catch(Error e){
        out.write("Couldn't connect to mysql");
    }
    String fname = req.getParameter("fname");
    String lname = req.getParameter("lname");
    String email = req.getParameter("email");
    String password = req.getParameter("password");
    String city = req.getParameter("city");
    String country = req.getParameter("country");

    if(fname == null){
        String destination = "signup.jsp?error=Complete All Fields";
        RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
        rd.forward(req, resp);
    }else if(lname == null){
        String destination = "signup.jsp?error=Complete All Fields";
        RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
        rd.forward(req, resp);
    }else if(email == null){
        String destination = "signup.jsp?error=Complete All Fields";
        RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
        rd.forward(req, resp);
    }else if(password == null){
        String destination = "signup.jsp?error=Complete All Fields";
        RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
        rd.forward(req, resp);
    }else if(city == null){
        String destination = "signup.jsp?error=Complete All Fields";
        RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
        rd.forward(req, resp);
    }else if(country == null){
        String destination = "signup.jsp?error=Complete All Fields";
        RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
        rd.forward(req, resp);
    }else{

        String sql = "INSERT INTO main.users(first_name, last_name, email, password, city, country, registered_time) VALUES("
                + fname +", "+ lname + ", "+ email +", " + password +", " + city +"," + country + ",Now());";
        int answer = mysql.insertSQL(sql);
        if(answer == 1){
            resp.sendRedirect( "index.jsp?registered=true");
            //String destination = "index.jsp?registered=true";
            //RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
            //rd.forward(req, resp);
        }
    }


}

 }

And this is the MySql Class to connect.

package com.app.pojo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySqlDB{

private static String username = "root", password = "root";

public Connection createConnection(){
    Connection connection = null;
    try{
        //Load the JDBC driver
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306,/main", username, password);
        //Create a connection to the database


    }catch(SQLException ex){
        System.out.println(ex);
    }catch(ClassNotFoundException e){
        System.out.println(e);
    }

    return connection;
}

public void runSqlStatement(String sql){
    try{
        Statement statement = createConnection().createStatement();
        //statement executeQuery(Query)
        boolean rs = statement.execute(sql);
    }catch(SQLException ex){
        System.out.println(ex);
    }
}

public ResultSet executeSQL(String sql){

    Statement statement = null;
    ResultSet rs = null;

    try{
        statement = createConnection().createStatement();
        rs = statement.executeQuery(sql);

        /*while(rs.next()){
            System.out.println(rs.getString(1));
        }*/


  //            rs.close();
  //            statement.close();
    }catch (SQLException e) {
        System.out.println(e);
    }

    return rs;
}

public int insertSQL(String sql){

    int rs;

    try{
        Statement statement = createConnection().createStatement();
        rs = statement.executeUpdate(sql);
        return rs;

    }catch(SQLException ex){
        System.out.println(ex);
        return 0;
    }


}
}

This is the tomcat console

INFO: Reloading Context with name [/Map] has started
Apr 21, 2012 12:59:14 AM org.apache.catalina.loader.WebappClassLoader clearReferencesJdbc
SEVERE: The web application [/Map] registered the JDBC driver [com.mysql.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
Apr 21, 2012 12:59:17 AM org.apache.catalina.core.StandardContext reload
INFO: Reloading Context with name [/Map] is completed

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 '********,Colombo,Sri Lanka,Now())' at line 1

Answer 1

Try this...

Connection con = mysql.createConnection();
String sql = "INSERT INTO main.users(first_name, last_name, email, password, city,
country, registered_time) VALUES(?, ?, ?, ?, ?, ?, ?);";
PreparedStatement insertStatement = con.prepareStatement(sql);
insertStatement.setString(1, first_name);
insertStatement.setString(2, last_name);
insertStatement.setString(3, email);
insertStatement.setString(4, password);
insertStatement.setString(5, city);
insertStatement.setString(6, country);
insertStatement.setString(7, new Date());
insertStatement.execute();

Greetings.

Answer 2

 String sql = "INSERT INTO main.users(first_name, last_name, email, password, city, country, registered_time) VALUES('"
                + fname +"', '"+ lname + "', '"+ email +"', '" + password +"',' " + city +"','" + country + "',Now())";

Answer 3

Colombo,Sri Lanka,Now())' at line 1

Looks like you're missing single quotes around your strings.

Answer 4

Try putting single quotes around your variables.

Ex:

VALUES('" + myString + "', '" + myOtherString + "')

Answer 5

You need to escape/quote the strings you have in your VALUES section. Your JDBC driver will do this for you, for instance using a PreparedStatement.

Note that you are in real danger of a SQL injection attack if you leave your code as-is, or just add surrounding quotes.