Silvia B Silvia B - 3 months ago 9
Java Question

How to use mysql-connector-driver with jsp

I need to create a login with JSP and so, I need to use

mysql-connector-java
.

I insert the file jar:
mysql-connector-java-5.1.38-bin.jar
into WEB-INF/lib

and I use this code into the jsp file:

<%@
page import="java.sql.*"
%>

<%
String DRIVER = "com.mysql.jdbc.Driver";
String URL_mioDB = "jdbc:mysql://localhost:3306/ditta";

try
{
Class.forName(DRIVER);
}
catch (ClassNotFoundException e)
{
System.err.println("Driver not found" + e);
}

Connection connessione = null;
try
{
// apro la connesione verso il database.
connessione = DriverManager.getConnection(URL_mioDB,"root","");
}
catch (Exception e)
{
System.err.println("Error during connection with db : " + e);
}

String mail="",pass="",send="",query="";

try
{
mail=request.getParameter("email");
pass=request.getParameter("password");
send=request.getParameter("send");

out.println("<FORM name='F1' method='post' action='login.jsp'>");
out.println("Email: <INPUT type='text' name='email' value='' placeholder='mariorossi@gmail.com'><BR><BR>");
out.println("Password: <INPUT type='password' name='password' value=''><BR><BR>");
out.println("<INPUT type='submit' name='send' value='Invia'>&nbsp;&nbsp;&nbsp;&nbsp;<INPUT type='reset' name='reset' value='Reset'>");
out.println("</FORM>");
}
catch (Exception e)
{
System.err.println(e);
}

if(send!=null && mail!="" && pass!="")
{
query="SELECT * FROM dipendenti WHERE email="+ mail + " AND password=" + pass + "";

Statement statement = connessione.createStatement();
ResultSet resultSet = statement.executeQuery(query);
ResultSetMetaData rsmd = resultSet.getMetaData();

for(int i=0;i<=rsmd.getColumnCount();i++)
{
out.println(resultSet.getString(i));
}
}

%>


after that, When I click on the send button the page give me this error:

errors image

Answer

Guessing a bit here...

I guess it might be about how you pass values of the parameters into the query. So if mail is "a@a.com" and password is "a", then your query ends up being:

SELECT * FROM dipendenti WHERE email=a@a.com AND password=a

This is not a proper SQL. You are missing the quotes/apostrophes. I'd try reaching a query like this:

SELECT * FROM dipendenti WHERE email='a@a.com' AND password='a'

which requires a simple change on how you define

query=...

At the same time, I know this is not what you are asking here, but I strongly suggest you should read about SQL Injection - either on Wikipedia or in StackOverflow Documentation. What you are doing here is not a proper way of accepting user input into your back-end program and eventually into the database.

For this very specific use-case JDBC (and databases) has a notion of Prepared Statements: http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

So you would start with something similar to:

    // not tested
    query="SELECT * FROM dipendenti WHERE email=? AND password=?";
    PreparedStatement statement = connessione.prepareStatement(query);

    statement.setString(1, mail);
    statement.setString(2, pass);

    ResultSet resultSet = statement.executeQuery();
Comments