SQL injection without single quotes
This SELECT must be passed as a Scalar Subquery enclosed in parentheses.
If date
is simply concatenated with text
then setting it to
' || (SELECT password FROM users WHERE username='admin') || '
results in
INSERT INTO data_table VALUES ([other values], '' || (SELECT password FROM users WHERE username='admin') || '')
which is valid SQL concatenating empty strings and the result of the SELECT. Now the DBMS will happily execute it :-)
Is it enough to forbid single quotes in input to avoid SQL injection?
Nope, it is not enough.
Yes, you have to take immediate action and change it to parameter usage where applicable.
Just a few guidelines for you to get it straight:
- Never take care of any injections. But make sure you have formatted your SQL literals properly. A properly formatted literal is error-proof and - just as a side effect - also invulnerable.
- Discover the fact that SQL query consists of literals of several different types. Each require distinct formatting, incompatible and useless for all others.
- Make sure such a formatting applied unconditionally. A prepared statement is the only way to be sure of.
SQL injection: isn't replace(', '') good enough?
No, it is not enough. It will do in a pinch, but it is a very weak alternative, and using parameterized queries or parameterized stored procedures is better, if your platform and/or RDBMS support either feature.
From
OWASP's SQL Injection Prevention Cheat Sheet
...this methodology is frail compared to using parameterized queries.
This technique should only be used, with caution, to retrofit legacy
code in a cost effective way.
There are more below
SQL injection — but why isn't escape quotes safe anymore?
Sql Injection Myths and Fallacies
SQL Injection after removing all single-quotes and dash-characters
Replace single quotes in SQL Server
You need to double up your single quotes as follows:
REPLACE(@strip, '''', '')
When to use single quotes, double quotes, and backticks in MySQL
Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.
Single quotes should be used for string values like in the VALUES()
list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.
MySQL also expects DATE
and DATETIME
literal values to be single-quoted as strings like '2001-01-01 00:00:00'
. Consult the Date and Time Literals documentation for more details, in particular alternatives to using the hyphen -
as a segment delimiter in date strings.
So using your example, I would double-quote the PHP string and use single quotes on the values 'val1', 'val2'
. NULL
is a MySQL keyword, and a special (non)-value, and is therefore unquoted.
None of these table or column identifiers are reserved words or make use of characters requiring quoting, but I've quoted them anyway with backticks (more on this later...).
Functions native to the RDBMS (for example, NOW()
in MySQL) should not be quoted, although their arguments are subject to the same string or identifier quoting rules already mentioned.
Backtick (`)
table & column ───────┬─────┬──┬──┬──┬────┬──┬────┬──┬────┬──┬───────┐
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`, `updated`)
VALUES (NULL, 'val1', 'val2', '2001-01-01', NOW())";
↑↑↑↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑↑↑↑↑
Unquoted keyword ─────┴┴┴┘ │ │ │ │ │ │ │││││
Single-quoted (') strings ───────────┴────┴──┴────┘ │ │ │││││
Single-quoted (') DATE ───────────────────────────┴──────────┘ │││││
Unquoted function ─────────────────────────────────────────┴┴┴┴┘
Variable interpolation
The quoting patterns for variables do not change, although if you intend to interpolate the variables directly in a string, it must be double-quoted in PHP. Just make sure that you have properly escaped the variables for use in SQL. (It is recommended to use an API supporting prepared statements instead, as protection against SQL injection).
// Same thing with some variable replacements
// Here, a variable table name $table is backtick-quoted, and variables
// in the VALUES list are single-quoted
$query = "INSERT INTO `$table` (`id`, `col1`, `col2`, `date`) VALUES (NULL, '$val1', '$val2', '$date')";
Prepared statements
When working with prepared statements, consult the documentation to determine whether or not the statement's placeholders must be quoted. The most popular APIs available in PHP, PDO and MySQLi, expect unquoted placeholders, as do most prepared statement APIs in other languages:
// PDO example with named parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)";
// MySQLi example with ? parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";
Characters requring backtick quoting in identifiers:
According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:
ASCII:
[0-9,a-z,A-Z$_]
(basic Latin letters, digits 0-9, dollar, underscore)
You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you must quote (backtick) them.
Also, although numbers are valid characters for identifiers, identifiers cannot consist solely of numbers. If they do they must be wrapped in backticks.
How does the SQL injection from the Bobby Tables XKCD comic work?
It drops the students table.
The original code in the school's program probably looks something like
q = "INSERT INTO Students VALUES ('" + FNMName.Text + "', '" + LName.Text + "')";
This is the naive way to add text input into a query, and is very bad, as you will see.
After the values from the first name, middle name textbox FNMName.Text (which is Robert'); DROP TABLE STUDENTS; --
) and the last name textbox LName.Text (let's call it Derper
) are concatenated with the rest of the query, the result is now actually two queries separated by the statement terminator (semicolon). The second query has been injected into the first. When the code executes this query against the database, it will look like this
INSERT INTO Students VALUES ('Robert'); DROP TABLE Students; --', 'Derper')
which, in plain English, roughly translates to the two queries:
Add a new record to the Students table with a Name value of 'Robert'
and
Delete the Students table
Everything past the second query is marked as a comment: --', 'Derper')
The '
in the student's name is not a comment, it's the closing string delimiter. Since the student's name is a string, it's needed syntactically to complete the hypothetical query. Injection attacks only work when the SQL query they inject results in valid SQL.
Edited again as per dan04's astute comment
Related Topics
Select Columnvalue If the Column Exists Otherwise Null
Differencebetween a Primary Key and a Unique Constraint
Oracle SQL: How to Use More Than 1000 Items Inside an in Clause
Calculate Row Wise Sum - SQL Server
How to Check If Identity_Insert Is Set to on or Off in SQL Server
Arithmetic Overflow Error Converting Numeric to Data Type Numeric
Extracting the Total Number of Seconds from an Interval Data-Type
Are There Any Free Tools to Generate 'Insert Into' Scripts in Ms SQL Server
Using Input from a Text File for Where Clause
MySQL Question - How to Handle Multiple Types of Users - One Table or Multiple
Will a SQL Server Job Skip a Scheduled Run If It Is Already Running
What Is the Data Type for Unix_Timestamp (Mysql)
Using Hibernate's Criteria and Projections to Select Multiple Distinct Columns
How to Store a String Var Greater Than Varchar(Max)
How to Compare Data Between Two Table in Different Databases Using SQL Server 2008
Update with Case and in - Oracle