SQL escaping and unescaping are essential when working with dynamic SQL queries, especially to avoid SQL injection attacks and ensure that special characters are properly handled. Here's how SQL escape/unescape can be done in Java and .NET for typical use cases.
1. SQL Escape/Unescape in Java
SQL escaping is commonly done by using prepared statements in Java to avoid SQL injection. However, if you need to manually escape or unescape SQL strings (e.g., for storing or displaying queries), you can handle it like this:
SQL Escaping in Java
When escaping for SQL queries, special characters like single quotes (') must be escaped as '' (double single quotes), and backslashes (\) may need to be escaped as \\.
java
public class SQLEscapeExample {
public static String escapeSQL(String input) {
// Escape single quotes by doubling them
return input.replace("'", "''");
}
public static void main(String[] args) {
String input = "O'Reilly";
String escaped = escapeSQL(input);
System.out.println(escaped); // Output: O''Reilly
}
}
This is the basic form of SQL escaping for preventing issues like breaking the query structure. Always prefer prepared statements in Java, which handle this automatically.
SQL Unescaping in Java
SQL unescaping typically involves replacing double single quotes ('') back with a single quote (').
java
public class SQLUnescapeExample {
public static String unescapeSQL(String input) {
// Replace doubled single quotes with a single quote
return input.replace("''", "'");
}
public static void main(String[] args) {
String escaped = "O''Reilly";
String unescaped = unescapeSQL(escaped);
System.out.println(unescaped); // Output: O'Reilly
}
}
2. SQL Escape/Unescape in .NET
In .NET, escaping and unescaping SQL queries is similar, but the best practice is to use parameterized queries to avoid manual escaping. Still, here's how you might manually escape and unescape SQL strings:
SQL Escaping in .NET
To escape SQL strings manually, you replace single quotes (') with double single quotes ('').
csharp
using System;
public class SQLEscapeExample
{
public static string EscapeSQL(string input)
{
// Escape single quotes by doubling them
return input.Replace("'", "''");
}
public static void Main()
{
string input = "O'Reilly";
string escaped = EscapeSQL(input);
Console.WriteLine(escaped); // Output: O''Reilly
}
}
SQL Unescaping in .NET
To unescape SQL strings, replace double single quotes ('') with a single quote (').
csharp
using System;
public class SQLUnescapeExample
{
public static string UnescapeSQL(string input)
{
// Replace doubled single quotes with a single quote
return input.Replace("''", "'");
}
public static void Main()
{
string escaped = "O''Reilly";
string unescaped = UnescapeSQL(escaped);
Console.WriteLine(unescaped); // Output: O'Reilly
}
}
Key Considerations for SQL Escape/Unescape:
Prepared Statements: The safest way to work with SQL queries is by using prepared statements or parameterized queries. These automatically handle escaping and prevent SQL injection attacks.
Example in Java with Prepared Statements:
java
String query = "SELECT * FROM users WHERE username = ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, userInput);
ResultSet rs = stmt.executeQuery();
Example in .NET with Parameterized Queries:
csharp
string query = "SELECT * FROM users WHERE username = @username";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@username", userInput);
SqlDataReader reader = cmd.ExecuteReader();
Escaping Special Characters: In SQL, besides single quotes, you may also need to escape other special characters like backslashes (\), depending on the SQL dialect and database you're using.
MySQL example:
Escape single quote: ''
Escape backslash: \\
Unescaping: When handling user input or query results, unescaping means converting back double quotes ('') into single quotes (').
Why Avoid Manual Escaping/Unescaping
Manual SQL escaping and unescaping should generally be avoided because:
Prepared Statements: These automatically handle escaping and are more secure.
Cross-Database Compatibility: Different databases have slightly different escape rules.
SQL Injection: Manual handling is prone to errors and security vulnerabilities. Using parameterized queries removes the risk of SQL injection.