藉助先行編譯防止sql注入攻擊

標籤：注入   size   添加使用者   更新   href   prepare   update   cep   添加   

可重用的sql操作類

 1 public ResultSet doQuery(String sql,Object[] params){ 2         ResultSet rs = null; 3         conn = this.getConnection(); 4         try{ 5             PreparedStatement pstmt = conn.prepareStatement(sql); 6             for(int i =0;i<params.length;i++){ 7                 pstmt.setObject(i+1, params[i]); 8             } 9             rs = pstmt.executeQuery();10         }catch(Exception e){11             e.printStackTrace();12         }13         return rs;14     }

 

 1 public int doUpdate(String sql,Object[] params){ 2         int res = 0; 3         conn  = this.getConnection(); 4         try{ 5             PreparedStatement pstmt = conn.prepareStatement(sql); 6             for(int i=0;i<params.length;i++){ 7                 pstmt.setObject(i+1, params[i]); 8             } 9             res = pstmt.executeUpdate();10         }catch(Exception e){11             e.printStackTrace();12         }13         return res;14     }

 

 1 public List<Object>  doQueryList(String sql,Object []params){ 2         List<Object> list = new ArrayList<Object>(); 3         ResultSet rs = this.doQuery(sql, params); 4         try{ 5             ResultSetMetaData rsmd  = rs.getMetaData(); 6             int columnLength = rsmd.getColumnCount(); 7             while(rs.next()){ 8                 Map<String,Object> map = new HashMap<String,Object>(); 9                 for(int i = 1;i<=columnLength;i++){10                     map.put(rsmd.getColumnLabel(i), rs.getObject(i));11                 }12                 list.add(map);13             }14         }catch(Exception e){ 15             e.printStackTrace();16         }17         return list;18     }

 

查詢所有資訊的jsp關鍵代碼如下

 1 <% 2     DBCon dbc = new DBCon(); 3     String sql  = "select * from schema.admin"; 4     List list = dbc.doQueryList(sql, new Object[]{}); 5      6     %> 7     <table border="1"> 8     <%for(int i =0;i<list.size();i++){ 9         Map<String,Object> map = (Map<String,Object>)list.get(i);10         %>11         <tr>12             <td><%=map.get("id") %></td>13             <td><%=map.get("username") %></td>14             <td><%=map.get("password") %></td>15             <td><a href="do_delete.jsp?id=<%=map.get("id")%>">刪除</a></td>16             <td><a href="do_edit.jsp?id=<%=map.get("id")%>">編輯</a></td>17         </tr>18     <%}19     dbc.close();20     %>21     </table>22     <a href="addAdmin.jsp">添加使用者</a>

更新、刪除登操作同理，不再列出。

 

藉助先行編譯防止sql注入攻擊