sql, jsp, java - why this insert into syntax do the false condition?

Question

I'm trying to make an input form and save them to mysql database using javabean and jsp. Since this is the first time I use jsp I don't have any idea why this program run the false condition for the insert into operation.

here's the code :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

public class Formulir {
    public String username;
    public String nama;
    public String ttl;
    public String alamat;
    public String telp;
    public String id_form;
    public Statement stmt=null;
    public Connection conn = null;
    public String sURL = "jdbc:odbc:formulir";

    public boolean insertIntoDB (String username, String nama, String ttl, String alamat, String telp, String id_form) {
        String sql = "insert into formulir values('"+username+"', '"+nama+"', '"+ttl+"', '"+alamat+"', '"+telp+"', '"+id_form+"')";
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection(sURL, "", "");
            stmt=conn.createStatement();
            stmt.execute(sql);
            stmt.close();
            return true;
        } catch(Exception e) {
            return false;
        }
    }

this one is the code for the input form process :

<jsp:useBean id="formulir" scope="session" class="BMS.Formulir" />
<jsp:setProperty name="formulir" property="*" />
<% if ((String)session.getAttribute("username")==null &&       (String)session.getAttribute("password")==null){
response.sendRedirect("daftar.jsp");} %>

<%
String username=(String)session.getAttribute("username");
String nama=request.getParameter("nama");
String ttl=request.getParameter("ttl");
String alamat=request.getParameter("alamat");
String telp=request.getParameter("telp");
String id_form="NULL";
%>

<html>
    <h2> <%=formulir.insertIntoDB(username, nama, ttl, alamat, telp, id_form)%> </h2>
</html>

and this is the structure for formulir table :

username (varchar)
nama (varchar)
ttl (varchar)
alamat (varchar)
telp (varchar)
id_form (int)

can you do inside the catch an e.printStackTrace() and add the output so we can see what's happening? — Manuel Pires, May 23 '13 at 13:51
Unless you are doing something I don't see behind the scenes, this risks SQL injection. — cwallenpoole, May 23 '13 at 13:53
Nobody can tell without more info, which will be provided by the stacktrace. Add the following line of code before returning false. e.printStackTrace(); This statement will print the stacktrace to the standard error output stream. Then edit your question by adding the stacktrace info. — nakosspy, May 23 '13 at 13:54
One more comment (irrelevant with your current problem). In an application server you can have connection pooling which is more efficient than opening a new connection every time you need one. — nakosspy, May 23 '13 at 13:56
please also close your connection inside finally block e.g. after the catch block do something like finally{ if (stmt != null) Try{stmt.close}catch(Throwable e){//loggin}} otherwise you will have something called connection pool leak — justMe, May 23 '13 at 14:06
false condition means what? did u mean wrong data or wrong condition in the flow — Francis Stalin, May 23 '13 at 14:09
@Francais Stalin yes, I mean it run the wrong condition in the flow — user2413755, May 23 '13 at 15:55

Joop Eggen · Answer 1 · 2013-05-23T14:44:52.180

Try this:

return the possible error message for testing
in SQL INSERT list the column names, so a later added field does not cause a regression error
Use a PreparedStatement. This prevent SQL injection hacking, and escapes single quotes and backslashes
Close the connection
Keep all local

Might need corrections. Look in the internet for usage of a DataSource in a web application. There exists a MySQL driver instead of the JDBC/ODBC bridge.

public String insertIntoDB (String username, String nama,
        String ttl, String alamat, String telp, String id_form) {
    String sql = "INSERT INTO formulir(username, nama, ttl, alamat, telp, id_form)"
        + " VALUES(?, ?, ?, ?, ?, ?)";
    try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection conn = DriverManager.getConnection(sURL, "", "");
        PreparedStatement stmt = conn.preparedStatement(stmt);
        stmt.setString(1, username);
        stmt.setString(2, nama);
        stmt.setString(3, ttl);
        stmt.setString(4, alamat);
        stmt.setString(5, telp);
        stmt.setInteger(6, Integer.parseInt(id_form));
        int count = stmt.executeUpdate();
        stmt.close();
        conn.disconnect();
        return "Updated: " + count;
    } catch(Exception e) {
        return e.getMessage();
    }
}

actually I don't really understand about the use of PreparedStatement but I'll try. Thanks for the answer :) — user2413755, May 23 '13 at 15:45
SQL injection is a subject in wikipedia; handling single quotes especially in values and other such conversion is nice. And for fun: http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — Joop Eggen, May 23 '13 at 15:52

score 2 · Answer 2 · edited May 23 '17 at 11:50

There are quite a few places where something could be going wrong, and your structure is masking them all. (This is one reason you want to use very specific Exception types if possible);

I think this the most likely problem: You are casting id_form as a SQL varchar by surrounding it with '. Change '"+id_form+"' to "+id_form+"
sun.jdbc.odbc.JdbcOdbcDriver may not be loaded. That would give you a ClassNotFoundException. You could create a catch(ClassNotFoundException e) block to look into that. One fix for that problem is presented in this question.
Your connection string does not allow you access to the DB. This would be an error thrown by DriverManager.getConnection, and it would be a SqlException. Add a catch(SqlException e) block and if you get an error, make sure that you can connect to the DB with the credentials in your config file.
You could also get an exception from createStatement, but that is likely caused by the same problem as getConnection.

On a semi-related note: you may wish to look into something called a PreparedStatement. Not only does that make it so that your code is less susceptible to attack, but it would also help you avoid that type of casting issue.

score 0 · Answer 3 · answered May 23 '13 at 14:03

0

Your id_form is in the table an int but you are putting the vlaue in ' '

answered May 23 '13 at 14:03

worcin

129
5

I've changed that but it still didn't work. Anyway, thanks for the answer :) – user2413755 May 23 '13 at 15:32

Alex · Answer 4 · 2013-05-23T14:07:44.357

-1

This is the Java code: String sql = "insert into formulir values('"+username+"',

Surrounding by single apostrophes means char value in Java

You probably would like the following for the Java string:

"'"+username +"'"

Because on screen my version looks similar to the original I am adding explanation:

It should be:

double quote, single quote, double quote, +, username, +, double quote, single quote, double quote. What he does is:

single quote, double quote, +, username, + double quote, single quote

edited May 23 '13 at 14:07

answered May 23 '13 at 13:57

Alex

7,007
18
69
114

Actually the ' are in the SQL statement, so totally fine – worcin May 23 '13 at 14:00
No, he is not doing this - just looks similar. It should be double quote, single quote, double quote, +, username, +, double quote, single quote, double quote. What he does is: single quote, double quote, +, username, + double quote, single quote – Alex May 23 '13 at 14:01
OP should be using [prepared statement](http://downloa.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html)s, instead of manually constructing his sql statement. – hd1 May 23 '13 at 14:02
@anarinsky Yes, but he is *starting* the string with a double quote, which means all of the single quotes are inside of double quotes. – cwallenpoole May 23 '13 at 14:08

sql, jsp, java - why this insert into syntax do the false condition?

4 Answers4