SQL Injection: Safe Login Check
Problem
Lines are user NAME PASSWORD (add a user) or login NAME PASSWORD (a login attempt). For each login line, print ok if a user with that name and that password exists, otherwise print denied. Names and passwords have no spaces, but they may contain quote characters or SQL words, for example admin'-- or x'OR'1'='1 (as the name, with any password). They must be treated as plain text, never as SQL.
Constraints
- 1 <= lines <= 100
- names and passwords have no spaces
Examples
Input
user admin secret login admin secret login admin'-- x
Output
ok denied
Solutions
Never build SQL by joining user text: with the text SELECT ... WHERE name = ' + name + ' an input like x'OR'1'='1 changes the meaning of the query and logs in without a password. A parameterized query sends the SQL and the values separately, so the database can never mistake a value for SQL. JavaScript and TypeScript use the built-in node:sqlite module (Node.js 22.5 or newer), which prints an experimental warning. Python uses the sqlite3 module that comes with Python.
Java needs a JDBC driver at run time. Download sqlite-jdbc from Maven Central (org.xerial:sqlite-jdbc) and run: java -cp .:sqlite-jdbc.jar Main (on Windows use ; instead of :).
/*
* Problem : Run the commands.
* Input : Input lines.
* Output : Output lines.
* Approach: Parameterized query
* Time : O(n)
* Space : O(users)
* Java : 8 or newer.
*/
import java.util.Scanner;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class Main {
// This method solves the task. It receives the input and returns the answer.
static String[] solve(String[] lines) {
List<String> out = new ArrayList<String>();
try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
try (Statement create = conn.createStatement()) {
create.execute("CREATE TABLE users (name TEXT PRIMARY KEY, password TEXT)");
}
try (PreparedStatement add = conn.prepareStatement("INSERT INTO users (name, password) VALUES (?, ?)");
// Step 1: The login query has ? places. Whatever the user types is sent as data, so quotes have no special meaning.
PreparedStatement check = conn.prepareStatement("SELECT COUNT(*) FROM users WHERE name = ? AND password = ?")) {
for (String line : lines) {
String[] part = line.split(" ");
if (part[0].equals("user")) {
add.setString(1, part[1]);
add.setString(2, part[2]);
add.executeUpdate();
} else {
check.setString(1, part[1]);
check.setString(2, part[2]);
// Step 2: COUNT(*) gives one row with one number. More than 0 means a match.
try (ResultSet rs = check.executeQuery()) {
rs.next();
out.add(rs.getInt(1) > 0 ? "ok" : "denied");
}
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return out.toArray(new String[0]);
}
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
java.util.List<String> all = new java.util.ArrayList<>();
while (scanner.hasNextLine()) {
String line = scanner.nextLine().trim();
if (!line.isEmpty()) {
all.add(line);
}
}
String[] lines = all.toArray(new String[0]);
System.out.println(String.join("\n", solve(lines)));
}
}
Hints (2)
- What would the joined SQL look like for the name x'OR'1'='1?
- Why can quotes in a parameter not change the query?