κ²°λ‘
queryλ¬Έμ λ£μ λλ
String query = "select * from user2 where name like '" + userName + "'";
μ΄λ κ² λ£μ§ λ§κ³
String query = "select * from user2 where name like ?";
μ΄λ κ² λ£μ
βμβ → 보μμ΄ μ·¨μ½ν΄μ§κΈ° λλ¬Έμ΄λ€.
Prepared Statement
public User getUser(String userName, String userPassword) {
Connection connection = getConnection();
String query = "select * from user2 where name like '" + userName + "'";
User user = new User();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = connection.prepareStatement(query);
rs = pstmt.executeQuery();
if (rs.next()) {
String password = rs.getString(4);
if (userPassword.equals(password)) {
System.out.println("λΉλ°λ²νΈ μΌμΉ " + password);
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setFullName(rs.getString(3));
user.setPassword(rs.getString(4));
user.setEmail(rs.getString(5));
user.setAge(rs.getInt(6));
user.setCreateDate(rs.getTimestamp(7));
}
} else {
user = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
μκ° λμ μ 체μ μΈ μ½λμ΄λ€.
λ¬Έμ κ° λλ λ° μ½λλ₯Ό 보μ.
String query = "select * from user2 where name like '" + userName + "'";
userNameμΌλ‘ μ‘°ννλ 쿼리문μ΄λ€.
userNameμ "λ 'μ΄ λ€μ΄κ°λ€λ©΄ μ€λ₯κ° λ μλ μκ³ , ν΄νΉ λΉν μλ μλ€.
(μ½λ 리뷰λ₯Ό ν΄μ£Όμ ¨λλ°, μ΄ λΆλΆμμ 보μ μ·¨μ½μ±μ΄ μλ€λ©΄μ μλ €μ£Όμ ¨λ€.)
β μ΄λ»κ² ν΄νΉμ νλκ±ΈκΉβ
곡격μκ° μ¬μ©μ μ λ ₯ λ°μ΄ν°λ₯Ό μ‘°μνμ¬ μνμ§ μλ 쿼리λ₯Ό μ€ννκ±°λ λ°μ΄ν°λ² μ΄μ€μ μ μμ μΈ λͺ λ Ήμ μ½μ ν μ μλ€.
μλ₯Ό λ€μ΄, userName λ³μμ '' OR '1'='1' μ κ°μ κ°μ μ λ ₯νλ©΄ SQL μΈμ μ 곡격μ μνν μ μλ€.
' OR '1'='1'λ νλμ μ°Έ 쑰건μ λνλ΄κΈ° λλ¬Έμ, λͺ¨λ λ μ½λκ° λ°νλ μ μλ€.
κ·Έλμ 곡격μλ μ΄ λ°©μμ μ¬μ©ν΄μ λ°μ΄ν°λ² μ΄μ€λ₯Ό μ‘°μνκ±°λ λ―Όκ°ν μ 보μ μ‘μΈμ€νλ €κ³ μλν μ μκΈ° λλ¬Έμ΄λ€.
κ·Έλμ SQL μΈμ μ 곡격μ λ°©μ§νλ €λ©΄ μ¬μ©μ μ λ ₯ λ°μ΄ν°λ₯Ό μμ νκ² μ²λ¦¬νκ³ , SQL 쿼리λ₯Ό ꡬμ±ν λλ Prepared Statements λλ ORMκ³Ό κ°μ μμ ν λ°©λ²μ μ¬μ©ν΄μΌ νλ€.
κ·Έλμ SQL 쿼리λ₯Ό 미리 μ»΄νμΌνκ³ , μ€νμμ 맀κ°λ³μ κ°μ λ°μΈλ©νλ€.
μ¬μ©μ μ λ ₯μ μλμΌλ‘ μ΄μ€μΌμ΄ννκ³ SQL μ½μ μ λ°©μ§νλ€.
κ·Έλμ λ°μ²λΌ λ°κΏ¨λ€.
public User getUser(String userName, String userPassword) {
Connection connection = getConnection();
String query = "select * from user2 where name like ?";
User user = new User();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = connection.prepareStatement(query);
pstmt.setString(1, userName);
rs = pstmt.executeQuery();
if (rs.next()) {
String password = rs.getString(4);
if (userPassword.equals(password)) {
System.out.println("λΉλ°λ²νΈ μΌμΉ " + password);
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setFullName(rs.getString(3));
user.setPassword(rs.getString(4));
user.setEmail(rs.getString(5));
user.setAge(rs.getInt(6));
user.setCreateDate(rs.getTimestamp(7));
}
} else {
user = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
String query = "select * from user2 where name like ?";
// λ°κΎΌ λΆλΆ
pstmt.setString(1, userName);
// μΆκ°ν λΆλΆ
μ΄λ κ² λ°κΎΈλ©΄μ 보μμ±μ 보쑴ν μ μμλ€.
μμ©
%μ λͺ©%μΌλ‘ κ²μνκΈ°.
%κ° λ€μ΄κ° μ λͺ©μΌλ‘ κ²μμ νκ³ μΆμ μλ μλ€. μ΄λ΄ λλ %λ₯Ό \\%λ‘ λ°κΏμ κ²μν μ μλ€.
쿼리문 : String subjectλ₯Ό μΈμλ‘ λ°λλ€.
if (subject == null) {
query = "SELECT a.id, a.subject, a.content, a.createDate, b.id AS creatorId, b.name AS creatorName, b.fullName AS creatorFullName "
+ " FROM boardpost a, user2 b " + " WHERE a.creatorId = b.id ";
} else {
query = "SELECT a.id, a.subject, a.content, a.createDate, b.id AS creatorId, b.name AS creatorName, b.fullName AS creatorFullName "
+ " FROM boardpost a, user2 b " + " WHERE a.subject like ? and a.creatorId = b.id ";
escapedSubject = (subject.contains("%")? subject.replace("%", "\\%"): subject);
}
λ§μ½ μ¬μ©μκ° μ λ ₯ν λ¬Έμμ΄μ %κ° λ€μ΄μλ€λ©΄ //%λ‘ λ체ν΄μ£ΌκΈ°
pstmt.setString(1, "%" + escapedSubject + "%");
κ·Έλ¦¬κ³ μλ€λ‘ %λ₯Ό λ£μ΄μ€μ ν¬ν¨ κ²μμ΄ κ°λ₯νκ² λ§λ€μ΄μ£ΌκΈ°
μ΄λ κ² ν΄μ 보μμ μ·¨μ½μ±μ΄ μ¬λΌμ‘λ€.
μ 체 λ©μλ
public List<BoardPost> getAllBoardPosts(String subject) {
List<BoardPost> boardPosts = new ArrayList<BoardPost>();
String query = "";
String escapedSubject = "";
// subjectμ κ°μ΄ μλ€λ©΄ κ²°κ³Όκ° λ³΄μ¬μ£ΌκΈ° / μλλΌλ©΄ μ 체 λͺ©λ‘ 보μ¬μ£ΌκΈ°
if (subject == null) {
query = "SELECT a.id, a.subject, a.content, a.createDate, b.id AS creatorId, b.name AS creatorName, b.fullName AS creatorFullName "
+ " FROM boardpost a, user2 b " + " WHERE a.creatorId = b.id ";
} else {
query = "SELECT a.id, a.subject, a.content, a.createDate, b.id AS creatorId, b.name AS creatorName, b.fullName AS creatorFullName "
+ " FROM boardpost a, user2 b " + " WHERE a.subject like ? and a.creatorId = b.id ";
escapedSubject = (subject.contains("%")? subject.replace("%", "\\%"): subject);
}
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet resultSet = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(query);
if(subject != null) {
pstmt.setString(1, "%" + escapedSubject + "%");
}
resultSet = pstmt.executeQuery();
while (resultSet.next()) {
User creator = new User();
long id = resultSet.getLong(1);
String postSubject = resultSet.getString(2);
String content = resultSet.getString(3);
Timestamp dateString = resultSet.getTimestamp(4);
creator.setId(resultSet.getLong(5));
creator.setName(resultSet.getString(6));
creator.setFullName(resultSet.getString(7));
boardPosts.add(new BoardPost(id, postSubject, content, creator, dateString));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (pstmt != null)
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (resultSet != null)
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return boardPosts;
}
'πΉπππ' μΉ΄ν κ³ λ¦¬μ λ€λ₯Έ κΈ
[Java] 10μ§μ β 2, 8, 16μ§μ λ³ν (0) | 2023.11.19 |
---|---|
[Java] "."μ κΈ°μ€μΌλ‘ splitν λλ \\μ μ¨μ€μΌ νλ€ / split(".") (0) | 2023.11.11 |
[JAVA] ArrayList removeIf / iterator.remove() / 리μ€νΈ μμ μμ νκΈ° (0) | 2023.11.02 |
JUnit Test Case μμ±μ μ μν μ : fail() (0) | 2023.10.30 |
[JAVA] java.lang.NumberFormatException (0) | 2023.10.29 |