java - Oracle database not getting updated using jsp.It's showing a blank page -
the problem when enter details in form , enter submit, shows blank .jsp page , database doesn't updated either.i have included odjbc.14 jar file in web-inf/lib folder , using oracle 10g. ide-eclipse. what's wrong in code??
this page connect database(oracle):
<body> <% string user = request.getparameter("login_id"); string pwd = request.getparameter("password"); string fname = request.getparameter("first_name"); string lname = request.getparameter("last_name"); string email = request.getparameter("email"); string sql="insert memebers(first_name,last_name,email,user_id,password,reg_date)values('"+fname+"','"+lname+"','"+email+"','"+user+"','"+pwd+"',curdate())"; try{ class.forname("oracle.jdbc.driver.oracledriver"); connection con = drivermanager.getconnection("jdbc:oracle:thin:nishtha/firstj2ee@//localhost:1521/xe"); int action=con.createstatement().executeupdate(sql); con.setautocommit(true); if(action>=1){ out.println("saved"); } else{ out.println("not saved"); } } catch (sqlexception e) { e.printstacktrace(); } %> </body> </html>
the problem blank page sql invalid , throws sqlexception. code ends in catch block , prints error app server log rather page. there several things in example better explained further below:
<%@ page language="java" contenttype="text/html; charset=iso-8859-1" pageencoding="iso-8859-1" import="java.sql.*,java.security.*" %> <!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1"> <title>test</title> </head> <body> <% string user = request.getparameter("login_id"); string pwd = request.getparameter("password"); string fname = request.getparameter("first_name"); string lname = request.getparameter("last_name"); string email = request.getparameter("email"); string sql = "insert members(first_name, last_name, email, user_id, password, reg_date)" + " values(?, ?, ?, ?, ?, sysdate)"; try { class.forname("oracle.jdbc.driver.oracledriver"); connection con = drivermanager .getconnection("jdbc:oracle:thin:nishtha/firstj2ee@//localhost:1521/xe"); con.setautocommit(false); preparedstatement stmt = con.preparestatement(sql); stmt.setstring(1, fname); stmt.setstring(2, lname); stmt.setstring(3, email); stmt.setstring(4, user); messagedigest md = messagedigest.getinstance("sha-256"); md.update(pwd.getbytes("utf-8")); byte[] digest = md.digest(); stmt.setstring(5, new string(digest, "utf-8")); int action = stmt.executeupdate(); if (action >= 1) { con.commit(); out.println("saved"); } else { out.println("not saved"); } } catch (sqlexception e) { e.printstacktrace(); out.println("error saving details:<br/>"); out.println(e.getmessage()); } %> </body> </html> 1.) better error handling: said, error ends in app server log. may absolutely fine may not want print stack traces , random errors on web pages little bit better error handling can present nicely user , print app server log. example if change
catch (sqlexception e) { e.printstacktrace(); } to code below start seeing error messages on page printing them app server log:
} catch (sqlexception e) { e.printstacktrace(); out.println("error saving details:<br/>"); out.println(e.getmessage()); } 2.) correct sql statement: after running example above (note, i've corrected typo in table , called members), error ora-00904: "curdate": invalid identifier. mentioned in comment need sysdate rather curdate:
string sql="insert members(first_name,last_name,email,user_id,password,reg_date)values('"+fname+"','"+lname+"','"+email+"','"+user+"','"+pwd+"',sysdate)"; now expected result: saved
note: sysdate keyword , therefore doesn't require (). there still more!
3.) committing data: mentioned in other answer, not committing data. may works fine because autocommit has default value of true or may work fine after first invocation of page because have set autocommit true in either case may have bad side effects can become tricky debug. best way ensure data safe committing explicitly changing
int action=con.createstatement().executeupdate(sql); con.setautocommit(true); if(action>=1){ out.println("saved"); } to execute actual commit , when have done insert:
con.setautocommit(false); int action = con.createstatement().executeupdate(sql); if (action >= 1) { con.commit(); out.println("saved"); } else { out.println("not saved"); } this may gives could not commit auto-commit set on on system because autocommit set true default. deactivate explicitly adding line:
con.setautocommit(false); 4.) prevent sql injection , poor performance: 1 of worst things can ever concatenate values directly sql statements! poor oracle performance database have hard parse statement , use more cpu doing it's worse security because can put in actual sql statements in what's meant single value. remedy use bind variables:
string sql = "insert members(first_name, last_name, email, user_id, password, reg_date)" + " values(?, ?, ?, ?, ?, sysdate)"; try { class.forname("oracle.jdbc.driver.oracledriver"); connection con = drivermanager .getconnection("jdbc:oracle:thin:nishtha/firstj2ee@//localhost:1521/xe"); con.setautocommit(false); preparedstatement stmt = con.preparestatement(sql); stmt.setstring(1, fname); stmt.setstring(2, lname); stmt.setstring(3, email); stmt.setstring(4, user); stmt.setstring(5, pwd); int action = stmt.executeupdate(); if (action >= 1) { con.commit(); out.println("saved"); } else { out.println("not saved"); } 5.) obfuscate passwords: security concern. storing passwords in clear text bad, bad indeed. don't need know users password , nor should dba able either doing simple select on table. nor should unix or storage admin grepping out of data files , nor network admin seeing flying on network in clear text. ideally encrypt password possible, in case apply sha-256 hash on it. on login page make sure again convert password sha-256 hash , compare hashes rather clear text passwords:
messagedigest md = messagedigest.getinstance("sha-256"); md.update(pwd.getbytes("utf-8")); byte[] digest = md.digest(); stmt.setstring(5, new string(digest, "utf-8")); int action = stmt.executeupdate();
Comments
Post a Comment