Interview question

How does PreparedStatement prevent SQL injection? PreparedStatement SQL injection को कैसे रोकता है?

Answer

PreparedStatement sends the SQL query structure to the database SEPARATELY from the parameter values. The database compiles the query first, then binds parameters strictly as data - they can never be interpreted as executable SQL, no matter what they contain.

Vulnerable (Statement with concatenation):
String sql = "SELECT * FROM users WHERE email = '" + userInput + "'";
Input: ' OR '1'='1 transforms the query into always-true, bypassing the WHERE clause entirely

Safe (PreparedStatement):

PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE email = ?");
ps.setString(1, userInput);

// Demonstrating the attack against raw Statement concatenation
String maliciousInput = "' OR '1'='1";
String sql = "SELECT * FROM users WHERE email = '" + maliciousInput + "'";
// Resulting query: SELECT * FROM users WHERE email = '' OR '1'='1'
// This returns ALL users, bypassing authentication entirely!

// PreparedStatement neutralizes this completely
PreparedStatement pstmt = conn.prepareStatement(
    'SELECT * FROM users WHERE email = ?');
pstmt.setString(1, maliciousInput);
// The database treats "' OR '1'='1" as a LITERAL STRING VALUE to search for,
// not as part of the SQL syntax - the query correctly finds zero matches

// Best practices to always follow:
// 1. NEVER build SQL by concatenating user input
// 2. ALWAYS use PreparedStatement with ? placeholders for any dynamic value
// 3. Use setXxx() methods (setString, setInt, etc.) instead of manual string building
// 4. Apply the principle of least privilege to the database user's permissions

PreparedStatement safeInsert = conn.prepareStatement(
    'INSERT INTO comments (user_id, text) VALUES (?, ?)');
safeInsert.setInt(1, userId);
safeInsert.setString(2, userComment);  // safe even if userComment contains SQL syntax
safeInsert.executeUpdate();

PreparedStatement SQL query structure को parameter values से अलग database को भेजता है। Database पहले query compile करता है, फिर parameters को strictly data की तरह bind करता है - वो कभी executable SQL नहीं बन सकते।

असुरक्षित (Statement concatenation):
String sql = "SELECT * FROM users WHERE email = '" + userInput + "'";
Input: ' OR '1'='1 query को हमेशा-true बना देता है

सुरक्षित (PreparedStatement):

PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE email = ?");
ps.setString(1, userInput);

String maliciousInput = "' OR '1'='1";
String sql = "SELECT * FROM users WHERE email = '" + maliciousInput + "'";
// यह सभी users return करता है, authentication bypass!

PreparedStatement pstmt = conn.prepareStatement(
    'SELECT * FROM users WHERE email = ?');
pstmt.setString(1, maliciousInput);
// Database इसे literal string value मानता है, SQL syntax नहीं

// हमेशा करें:
// 1. कभी user input concatenate न करें
// 2. हमेशा PreparedStatement ? placeholders से use करें
// 3. setXxx() methods use करें

PreparedStatement safeInsert = conn.prepareStatement(
    'INSERT INTO comments (user_id, text) VALUES (?, ?)');
safeInsert.setInt(1, userId);
safeInsert.setString(2, userComment);
safeInsert.executeUpdate();

Was this answer clear?

Subjects

Related questions