conn = pool.getConnection( ); String sql = "select * from user where username='" + username +"' and password='" + password + "'"; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { loggedIn = true; out.println("Successfully logged in"); } else { out.println("Username and/or password not recognized"); }
為避免執行sql語句讓hacker使用sql injection,可改用PreparedStatement物件
String selectStatement = "SELECT * FROM User WHERE userId = ? "; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, userId); ResultSet rs = prepStmt.executeQuery();
如何使用PreparedStatement執行批次
import java.util.*; import java.sql.*; import javax.sql.*; import javax.naming.*; public class MySqlPreparedStatementBatch { public static void main(String [] args) { Connection con = null; try { com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource(); ds.setServerName("localhost"); ds.setPortNumber(3306); ds.setDatabaseName("HerongDB"); ds.setUser("Herong"); ds.setPassword("TopSecret"); con = ds.getConnection(); // PreparedStatement PreparedStatement ps = con.prepareStatement( "INSERT INTO Profile (FirstName, LastName) VALUES (?, ?)"); // Provide values to parameters for copy 1 ps.setString(1,"John"); ps.setString(2,"First"); // Create copy 1 ps.addBatch(); // Provide values to parameters for copy 2 ps.setString(1,"Bill"); ps.setString(2,"Second"); // Create copy 2 ps.addBatch(); // Provide values to parameters for copy 3 ps.setString(1,"Mark"); ps.setString(2,"Third"); // Create copy 3 ps.addBatch(); // Provide values to parameters for copy 4 ps.setString(1,"Jack"); ps.setString(2,"Last"); // Create copy 4 ps.addBatch(); // Execute all 4 copies int[] counts = ps.executeBatch(); int count = 0; for (int i=0; i<counts.length; i++) { count += counts[i]; } System.out.println("Total effected rows: "+count); // Close the PreparedStatement object ps.close(); con.close(); } catch (Exception e) { System.err.println("Exception: "+e.getMessage()); e.printStackTrace(); } } }Reference:
Preventing SQL Injection in Java PreparedStatement in Batch Mode
如何在 Java 網站應用程式中防範 SQL Injection
沒有留言:
張貼留言
留個話吧:)