SQL Injection A bad code and Good Fix Example
By Admin
•
November 9, 2025
Bad example — classic SQL Injection (trash)
// BAD: vulnerable to SQL Injection
// Example: /login?username=admin'--&password=whatever
public class AuthServlet extends HttpServlet {
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
String user = req.getParameter("username");
String pass = req.getParameter("password");
try (Connection conn = DataSource.getConnection();
Statement stmt = conn.createStatement()) {
// DANGEROUS: concatenating untrusted input directly into SQL
String sql = "SELECT id, username FROM users WHERE username = '" + user +
"' AND password = '" + pass + "'";
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
resp.getWriter().println("Welcome " + rs.getString("username"));
} else {
resp.getWriter().println("Invalid credentials");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
Why it's broken
- User-controlled values are concatenated into SQL → attacker injects '; DROP TABLE users; -- or bypasses auth with admin' --.
- No parameterization, no input control, no least-privilege DB user assumption.
- Credentials may be stored in cleartext (implied), no hashing.
✅ Good fix — JDBC with PreparedStatement (parameterized)
// GOOD: parameterized query with hashed password + try-with-resources
public class AuthServlet extends HttpServlet {
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
String user = req.getParameter("username");
String pass = req.getParameter("password");
// 1) Validate minimal shape (length, allowed chars) - whitelist when possible
if (user == null || pass == null || user.length() > 100) {
resp.sendError(HttpServletResponse.SC_BAD_REQUEST);
return;
}
// 2) Hash the incoming password using a secure storage/compare routine (e.g., bcrypt)
String hashedInput = PasswordUtils.hashForCompare(pass); // uses bcrypt/argon2 in production
String sql = "SELECT id, username FROM users WHERE username = ? AND password_hash = ?";
try (Connection conn = DataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, user);
ps.setString(2, hashedInput); // never store or compare plain-text passwords
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
resp.getWriter().println("Welcome " + rs.getString("username"));
} else {
resp.getWriter().println("Invalid credentials");
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
Why this is better
- PreparedStatement binds values — prevents injection.
- Input minimal validation reduces risk and DoS vectors.
- Passwords compared as hashes (use bcrypt/argon2 with per-user salt).
- Use least-privilege DB account (only SELECT for auth) and connection pool.
✅ Good alternative — JPA / Hibernate (safe parameter binding)
// GOOD: Using JPA with named parameters (no string concat)
@Stateless
public class AuthService {
@PersistenceContext
private EntityManager em;
public User authenticate(String username, String plainPassword) {
if (username == null) return null;
TypedQuery<User> q = em.createQuery(
"SELECT u FROM User u WHERE u.username = :username", User.class);
q.setParameter("username", username);
List<User> users = q.getResultList();
if (users.isEmpty()) return null;
User u = users.get(0);
if (PasswordUtils.verify(plainPassword, u.getPasswordHash())) {
return u;
}
return null;
}
}
Why this is good
- JPA parameter binding avoids manual SQL concatenation.
- Move business logic & crypto outside DB string builds.
- ORM reduces injection risk when used correctly (avoid createQuery() with concatenated strings).
Additional hardening — short checklist (non-negotiable)
- Always use parameterized queries (PreparedStatement, Named parameters, ORM binding).
- Never build SQL with user input (including column/table names) — if you must accept identifiers, whitelist them server-side.
- Hash & salt passwords with bcrypt/argon2; never store plain text.
- Least-privilege DB user: app DB user should have only required rights (SELECT/INSERT/UPDATE only as needed).
- Use stored procedures only with parameters (not dynamic SQL inside SPs).
- Escape properly only as last resort — prefer parameterization.
- Logging & monitoring for abnormal queries and repeated auth failures.
- Test: run dynamic scanning (DAST) and SAST to catch unsafe patterns.
- Prepare for errors: don't leak SQL or stack traces to users. Return generic errors.