Why should you not use CONCAT() for static string literals?

Question

So you have

$sql = "SELECT * FROM `table` WHERE `some_text_field` LIKE CONCAT('%', ?, '%')";
$stmt = $dbh->prepare($sql);
$stmt->execute(array($_POST['badies_code']));

And looking at another question i found that this causes a security concern, but why?

I found this question, a downvoted answer and an upvoted comment that is why i ask

The comment said

This is not the correct way to do this. You should not use CONCAT() for three static string literals, as it opens you up to a specific type of SQL injection (i forget the name). – Theodore R. Smith

PHP PDO prepared statement -- mysql LIKE query

Your Common Sense · Answer 1 · 2014-03-30T08:43:46.450

2

It would be quite a complex task to remember the name of injection that doesn't exist.

There is nothing wrong in using concat() with prepared statement.

edited Mar 30 '14 at 08:43

answered Mar 30 '14 at 04:51

Your Common Sense

156,878
40
214
345

Can you expand on what you mean when using the terms "intentional" and "strict". Thank you – Victory Mar 30 '14 at 04:53
strict means strict, when you need exact value to match the given one. intentionally means intentionaly partial match. Anyway, it's only my speculations trying to explain that idea of non-existent "injection" – Your Common Sense Mar 30 '14 at 04:59
What do you think about Gumbo's response? – Victory Mar 30 '14 at 10:01
inapplicable for mysql – Your Common Sense Mar 30 '14 at 10:18
1

@Victory Without a statement from @TheodoreR.Smith we can’t do anything more than just (educated) guesses. I can’t imagine a situation either in which using `CONCAT` can be dangerous except from building SQL statements dynamically within stored procedures. And the *Lateral SQL Injection* technique shows that one may be vulnerable even though there is no explicit parameter involved. However, prepared statements is *the* mitigation technique that should be used whenever parameters are involved. – Gumbo Mar 30 '14 at 10:48

Gumbo · Accepted Answer · 2014-03-30T08:44:15.487

I think what @TheodoreR.Smith may have meant is the so called Lateral SQL Injection in Oracle Database^[1][2].

It works by changing environment variables holding format information such as NLS_DATE_FORMAT, or NLS_NUMERIC_CHARACTERS, which are then used in a stored procedure that builds and executes a statement dynamically (this is where string concatenation is used, denoted by the || operators):

CREATE OR REPLACE PROCEDURE date_proc IS
    stmt VARCHAR2(200);
    v_date DATE := SYSDATE;
BEGIN
    stmt := 'select object_name from all_objects where created = ''' || v_date || '''';
    EXECUTE IMMEDIATE stmt;
END;

Here SYSDATE returns the current date in the format specified in NLS_DATE_FORMAT. Although the procedure has no parameter, changing the date format to something like ' or 1=1--:

ALTER SESSION SET NLS_DATE_FORMAT = ''' or 1=1--'

The resulting statement is:

select object_name from all_objects where created = '' or 1=1--'

This environment variable manipulation is specific to Oracle Database. And again, it can be mitigated using prepared statements:

CREATE OR REPLACE PROCEDURE date_proc IS
    stmt VARCHAR2(200);
    v_date DATE := SYSDATE;
BEGIN
    stmt := 'select object_name from all_objects where created = :date';
    EXEC SQL PREPARE prepared_stmt FROM :stmt;
    EXEC SQL EXECUTE prepared_stmt USING :v_date;
end;

I’m not aware that MySQL is prone to this kind of environment variable manipulation.

However, building statements dynamically without proper processing is prone to SQL injections, no matter whether it happens in the application or in the database. So using prepared statements in stored procedures is mandatory as well.

Why should you not use CONCAT() for static string literals?

2 Answers2

Linked