SQL注入

SQL注入 维基百科

PreparedStatement

Secure Usage

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE userid=? AND password=?");
stmt.setString(1, userid);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();

Vulnerable Usage

// Example #1
String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);
// Example #2
String query = "SELECT * FROM users WHERE userid ='"+ userid + "'" + " AND password='" + password + "'";
PreparedStatement stmt = connection.prepareStatement(query);
ResultSet rs = stmt.executeQuery();

Hibernate

Secure Usage

/* Positional parameter in HQL */
Query hqlQuery = session.createQuery("from Orders as orders where orders.id = ?");
List results = hqlQuery.setString(0, "123-ADB-567-QTWYTFDL").list();

/* named parameter in HQL */
Query hqlQuery = session.createQuery("from Employees as emp where emp.incentive > :incentive");
List results = hqlQuery.setLong("incentive", new Long(10000)).list();

/* named parameter list in HQL */
List items = new ArrayList(); 
items.add("book"); items.add("clock"); items.add("ink");
List results = session.createQuery("from Cart as cart where cart.item in (:itemList)").setParameterList("itemList", items).list();

/* JavaBean in HQL */
Query hqlQuery = session.createQuery("from Books as books where book.name = :name and book.author = :author");
List results = hqlQuery.setProperties(javaBean).list(); //assumes javaBean has getName() & getAuthor() methods.

/* Native-SQL */
Query sqlQuery = session.createSQLQuery("Select * from Books where author = ?");
List results = sqlQuery.setString(0, "Charles Dickens").list();

Vulnerable Usage

List results = session.createQuery("from Orders as orders where orders.id = " + currentOrder.getId()).list();
List results = session.createSQLQuery("Select * from Books where author = " + book.getAuthor()).list();

JPA

Secure Usage

/* positional parameter in JPQL */
Query jpqlQuery = entityManager.createQuery("Select order from Orders order where order.id = ?1");
List results = jpqlQuery.setParameter(1, "123-ADB-567-QTWYTFDL").getResultList();

/* named parameter in JPQL */
Query jpqlQuery = entityManager.createQuery("Select emp from Employees emp where emp.incentive > :incentive");
List results = jpqlQuery.setParameter("incentive", new Long(10000)).getResultList();

/* named query in JPQL - Query named "myCart" being "Select c from Cart c where c.itemId = :itemId" */
Query jpqlQuery = entityManager.createNamedQuery("myCart");
List results = jpqlQuery.setParameter("itemId", "item-id-0001").getResultList();

/* Native SQL */
Query sqlQuery = entityManager.createNativeQuery("Select * from Books where author = ?", Book.class);
List results = sqlQuery.setParameter(1, "Charles Dickens").getResultList();

Vulnerable Usage

List results = entityManager.createQuery("Select order from Orders order where order.id = " + orderId).getResultList();
List results = entityManager.createNativeQuery("Select * from Books where author = " + author).getResultList();
int resultCode = entityManager.createNativeQuery("Delete from Cart where itemId = " + itemId).executeUpdate();

MyBatis

Secure Usage

<select id="getPerson" parameterType="int" resultType="org.application.vo.Person">
SELECT * FROM PERSON WHERE ID = #{id}
</select>

/* Comparable JDBC code */
String selectPerson = "SELECT * FROM PERSON WHERE ID = ?"; 
PreparedStatement ps = conn.prepareStatement(selectPerson); 
ps.setInt(1, id);

<insert id="insertPerson" parameterType="org.application.vo.Person">
insert into Person (id, name, email, phone)
values (#{id}, #{name}, #{email}, #{phone})
</insert>
 
<update id="updatePerson" parameterType="org.application.vo.Person">
update Person set name = #{name}, email = #{email}, phone = #{phone}
where id = #{id}
</update>
 
 
<delete id="deletePerson" parameterType="int">
delete from Person where id = #{id}
</delete>

Vulnerable Usage

<select id="getPerson" parameterType="string" resultType="org.application.vo.Person">
SELECT * FROM PERSON WHERE NAME = #{name} AND PHONE LIKE '${phone}'; 
</select>

<insert id="insertPerson" parameterType="org.application.vo.Person">
insert into Person (id, name, email, phone)
values (#{id}, #{name}, #{email}, ${phone})
</insert>
 
<update id="updatePerson" parameterType="org.application.vo.Person">
update Person set phone = ${phone}
where id = #{id}
</update>
 
 
<delete id="deletePerson" parameterType="int">
delete from Person where id = ${id}
</delete>