数据库CH--(9)

CHAPTER9ApplicationDesignandDevelopmentExercises9.12WriteaservletandassociatedHTMLcodeforthefollowingverysimpleapplication:Auserisallowedtosubmitaformcontainingavalue,sayn,andshouldgetaresponsecontainingn“*”symbols.Answer:HTMLformhtmlheadtitleDBBookExercise8.8/title/headformaction=”servlet/StarServlet”method=getEnterthevaluefor“n”brinputtype=textsize=5name=”n”inputtype=submitvalue=”submit”/form/htmlServletCode7778Chapter9ApplicationDesignandDevelopmentimportjava.io.*;importjavax.servlet.*;importjavax.servlet.http.*;publicclassStarServletextendsHttpServlet{publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{intn=Integer.parseInt(request.getParameter(n));response.setContentType(“text/html”);PrintWriterout=response.getWriter();out.println(HEADTITLEExercise8.8/TITLE/HEAD);out.println(BODY);for(inti=0;in;i++){out.print(*);}out.println(/BODY);out.close();}}9.13WriteaservletandassociatedHTMLcodeforthefollowingsimpleappli-cation:Auserisallowedtosubmitaformcontaininganumber,sayn,andshouldgetaresponsesayinghowmanytimesthevaluenhasbeensubmittedpreviously.Thenumberoftimeseachvaluehasbeensubmittedpreviouslyshouldbestoredinadatabase.Answer:HTMLformhtmlheadtitleDBBookExercise9.13/title/headformaction=servlet/KeepCountServletmethod=getEnterthevaluefornbrinputtype=textsize=5name=ninputtype=submitvalue=submit/form/htmlSchemaCREATETABLESUBMISSIONCOUNT(valueintegerunique,scountintegernotnull);Exercises79ServletCodeimportjava.io.*;importjava.sql.*;importjava.util.*;importjavax.servlet.*;importjavax.servlet.http.*;publicclassKeepCountServletextendsHttpServlet{privatestaticfinalStringquery=“SELECTscountFROMSUBMISSIONCOUNTWHEREvalue=?”;publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{intn=Integer.parseInt(request.getParameter(“n”));intcount=0;try{Connectionconn=getConnection();PreparedStatementpstmt=conn.prepareStatement(query);pstmt.setInt(1,n);ResultSetrs=pstmt.executeQuery();if(rs.next()){count=rs.getInt(1);}pstmt.close();Statementstmt=conn.createStatement();if(count==0){stmt.executeUpdate(“INSERTINTOSUBMISSIONCOUNTVALUES(”+n+“,1)”);}else{stmt.executeUpdate(“UPDATESUBMISSIONCOUNTSET”+“scount=scount+1WHEREvalue=”+n);}stmt.close();conn.close();}catch(Exceptione){thrownewServletException(e.getMessage());}response.setContentType(“text/html”);PrintWriterout=response.getWriter();out.println(HEADTITLEExercise9.13/TITLE/HEAD);out.println(BODY);out.println(“Thevalue”+n+“hasbeensubmitted”+count+“timespreviously.”);out.println(/BODY);out.close();}}80Chapter9ApplicationDesignandDevelopment9.14Writeaservletthatauthenticatesauser(basedonusernamesandpass-wordsstoredinadatabaserelation),andsetsasessionvariablecalleduseridafterauthentication.Answer:HTMLformhtmlheadtitleDBBookExercise9.14/title/headformaction=“servlet/SimpleAuthServlet”method=getUserName:inputtype=textsize=20name=“user”BRBRPassword:inputtype=passwordsize=20name=“passwd”BRinputtype=submitvalue=“submit”/form/htmlSchemaCREATETABLEUSERAUTH(useridintegerprimarykey,usernamevarchar(100)unique,passwordvarchar(20));ServletCodeExercises81importjava.io.*;importjava.sql.*;importjava.util.*;importjavax.servlet.*;importjavax.servlet.http.*;publicclassSimpleAuthServletextendsHttpServlet{privatestaticfinalStringquery=“SELECTuserid,passwordFROMUSERAUTHWHEREusername=?”;publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{Stringuser=request.getParameter(“user”);Stringpasswd=request.getParameter(“passwd”);StringdbPass=null;intuserId=-1;try{Connectionconn=getConnection();PreparedStatementpstmt=conn.prepareStatement(query);pstmt.setString(1,user);ResultSetrs=pstmt.executeQuery();if(rs.next()){userId=rs.getInt(1);dbPass=rs.getString(2);}pstmt.close();conn.close();}catch(Exceptione){thrownewServletException(e.getMessage());}Stringmessage;if(passwd.equals(dbPass)){message=“Authenticationsuccessful”;getServletContext().setAttribute(“userid”,newInteger(userId));}else{message=“Authenticationfailed!Pleasechecktheusername”+“andpassword.”;}response.setContentType(“text/html”);PrintWriterout=response.getWriter();out.println(“HEADTITLEExercise9.14/TITLE/HEAD”);out.println(“BODY”);out.println(message);out.println(“/BODY”);out.close();}}82Chapter9ApplicationDesignandDevelopment9.15WhatisanSQLinjectionattack?Explainhowitworks,andwhatprecau-tionsmustbetakentopreventSQLinjectionattacks.Answer:SQLinjectionattackoccurswhenamalicioususer(attacker)managestogetanapplicationtoexecuteanSQLquerycreatedbytheattacker.IfanapplicationconstructsanSQLquerystringbyconcatenatingtheusersuppliedparameters,theapplicationispronetoSQLinjectionattacks.Forexample,supposeanapplicationconstructsandexecutesaquerytoretrieveauser’spasswordinthefollowingway:Stringuserid=request.getParameter(“userid”);executeQuery(“SELECTpasswordFROMuserinfoWHEREuserid=’”+userid+“’”);Now,ifausertype