SQL Injection: From Attack to Defense (What I Learned at My Internship)

SQL injection has been on the OWASP Top 10 for over a decade. It's one of the oldest, best-understood web vulnerabilities in existence. And yet it still appears — in enterprise applications, in startups, in government systems — because the gap between knowing about it and actually preventing it in every code path is larger than most people realize.

This post draws directly from my internship at Sonata Software, where I implemented secure database interactions as part of a Java CRM system. I'll show you how SQL injection works, what makes it so dangerous, and every layer of defense we applied.

What SQL Injection Actually Is

When an application constructs a SQL query by concatenating user-supplied input directly into the query string, an attacker can inject SQL syntax that changes the query's logic.

Classic example:

// NEVER do this
String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

If an attacker enters admin' -- as the username and anything as the password, the query becomes:

SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'

The -- is a SQL comment — everything after it is ignored. The password check is bypassed entirely. The attacker is authenticated as admin.

More dangerous: ' OR '1'='1 as both username and password produces:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1'

This returns every row in the users table.

What Can an Attacker Do?

SQL injection isn't just an authentication bypass. Depending on the database configuration and query type, attackers can:

• Dump entire databases: UNION SELECT attacks extract data from other tables
• Modify data: INSERT, UPDATE, DELETE through stacked queries (where the database supports them)
• Execute OS commands: MySQL's LOAD_FILE() and INTO OUTFILE, MSSQL's xp_cmdshell can reach beyond the database to the operating system
• Bypass all authentication and authorization controls

The Primary Fix: Parameterized Queries

The definitive fix for SQL injection is parameterized queries (prepared statements). In a parameterized query, the SQL structure is defined first, then user input is passed as a parameter. The database driver handles escaping and type enforcement — user input can never become part of the query's logic.

In Java (JDBC):

// VULNERABLE
String query = "SELECT * FROM customers WHERE email = '" + email + "'";
ResultSet rs = stmt.executeQuery(query);

// SAFE — Parameterized query
String query = "SELECT * FROM customers WHERE email = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, email);
ResultSet rs = pstmt.executeQuery();

In our CRM at Sonata Software, every database interaction was refactored to use PreparedStatement. The ? placeholder is never interpreted as SQL — it's just a value. An attacker's SQL payload becomes a harmless string.

With JPA/Hibernate (common in Spring Boot):

// VULNERABLE — String interpolation in JPQL
@Query("SELECT u FROM User u WHERE u.email = '" + email + "'")

// SAFE — Named parameter
@Query("SELECT u FROM User u WHERE u.email = :email")
User findByEmail(@Param("email") String email);

With MyBatis:

<!-- VULNERABLE — ${} evaluates as literal SQL -->
<select id="findUser">SELECT * FROM users WHERE name = '${name}'</select>

<!-- SAFE — #{} uses parameterized binding -->
<select id="findUser">SELECT * FROM users WHERE name = #{name}</select>

Stored Procedures

Stored procedures can be safe or unsafe depending on how they're implemented.

-- SAFE stored procedure — parameterized internally
CREATE PROCEDURE GetCustomer @CustomerID INT
AS
BEGIN
    SELECT * FROM Customers WHERE CustomerID = @CustomerID
END

CallableStatement cstmt = conn.prepareCall("{call GetCustomer(?)}");
cstmt.setInt(1, customerId);

But stored procedures that dynamically construct SQL internally with EXEC or sp_executesql without parameterization are still vulnerable.

Input Validation (Defense in Depth)

Parameterized queries are the primary defense. Input validation is a secondary layer that helps catch issues before they reach the database.

In our CRM, I implemented a validation layer using Java Bean Validation:

public class CustomerDTO {
    @NotNull
    @Email(message = "Invalid email format")
    private String email;
    
    @Pattern(regexp = "^[A-Za-z\\s]{1,100}$", message = "Name must contain only letters")
    private String name;
    
    @Digits(integer = 10, fraction = 0, message = "Phone must be numeric")
    private String phone;
}

Allowlist validation (defining what's acceptable) is stronger than denylist validation (blocking known bad characters). An allowlist for a phone number field — digits only — is trivially defined and impossible to bypass. A denylist trying to catch all SQL injection characters will always have gaps.

Least Privilege Database Accounts

This defense doesn't prevent injection from succeeding, but it limits the blast radius dramatically.

If your application's database user can only SELECT from the tables it needs, a SQL injection vulnerability can't be used to DROP tables or execute OS commands. We applied this during our CRM implementation:

-- Create application-specific user with minimal permissions
CREATE USER 'crm_app'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE ON crm_db.customers TO 'crm_app'@'localhost';
GRANT SELECT, INSERT ON crm_db.interactions TO 'crm_app'@'localhost';
-- NO DELETE, NO DROP, NO FILE permissions

Error Handling: Don't Leak Information

Database error messages reveal the database type, table structure, and query logic to an attacker. Never return raw database errors to users.

try {
    // database operation
} catch (SQLException e) {
    // Log the full error internally
    logger.error("Database error: {}", e.getMessage(), e);
    
    // Return a generic message to the user — no SQL details
    throw new ApplicationException("An error occurred processing your request.");
}

Verbose error messages are a common reconnaissance tool. Attackers use error-based SQL injection specifically to extract database structure from error output.

Web Application Firewall (WAF)

A WAF provides a network-level layer of defense — it inspects HTTP traffic and blocks requests containing known SQL injection patterns. We used ModSecurity in our deployment.

A WAF is a valuable defense-in-depth layer, but it's not a substitute for secure code. Sophisticated attackers use encoding (URL encoding, hex encoding, Unicode) and other obfuscation techniques to bypass WAF rules. Parameterized queries in the application code remain the essential control.

Testing Your Own Code

After refactoring the CRM code, I used SQLMap in a controlled environment to test our implementation:

sqlmap -u "http://crm-test/api/customers?email=test@test.com" \
  --cookie="session=..." \
  --level=5 --risk=3 \
  --batch

SQLMap confirmed zero injectable parameters after our changes — every endpoint returned parameterized query errors or rejected the tampered requests at the validation layer.

The Takeaway

SQL injection is preventable. Not "hard to prevent" — genuinely preventable. Parameterized queries eliminate the vulnerability class entirely in the endpoints where they're applied.

The challenge is consistency: every database interaction in every code path must use parameterized queries. One overlooked endpoint — a rarely-used admin function, a legacy module that wasn't refactored — is all an attacker needs.

Code review processes that specifically check for string concatenation in SQL queries, combined with automated static analysis tools (SonarQube, Checkmarx, or SpotBugs with Find Security Bugs plugin for Java), are the best ways to catch remaining issues.

We automated validation and cleanup workflows at Sonata, reducing manual errors by 25%. That same discipline applied to security review — systematic, automated, not dependent on individual memory — is how you keep injection vulnerabilities out of production.