Are multiline queries sql-injection safe?

Question

This might be a stupid question. Or maybe my hacking skills are limited (I don't practice them at all).

I have a query that looks like this:

<?php
$query =<<<eot
    SELECT      table_x.field1,
                table_x.field2,
                table_y.*,
                table_z.field4
    FROM        (
                    SELECT ...
                ) as table_y
    LEFT JOIN   table_x
    ON          table_x.field1 = table_y.field_x
    LEFT JOIN   table_z
    ON          table_z.field1 = table_y.field_z
    WHERE       table_x.field3 = '$something'
    AND         table_z.field4 = '1'
    AND         table_z.field5 = '2'
eot;
?>

I have a lot of other tests on $something before it gets used, like $something = explode(' ',$something); (which later result in a string) none of them intend to prevent injection but they make it hard for the given injection to get as is to the actual query. However, there are ways. We all know how easy it is to replace a space for something else which is still valid..

So, it's not really a problem to make a potentially harmful piece of SQL reach that $something... But is there any way to comment the rest of the original query string if it is multi-line?

I can comment AND table_z.field4 = '1' using ;-- but can't comment the following AND table_z.field5 = '2'

Is it possible to open a multi-line comment /* without closing it or something looked like and therefore allow the injection to ignore the multi-line query?

Answer 1

It's not safe. Even if it can't comment out the rest, it could prefix it with SELECT * FROM my_table WHERE 1=1.

Answer 2

$something = "'; DROP TABLE table_x; SELECT * FROM table_z WHERE '1' = '1";

Answer 3

Use prepared statements to be safe:

http://www.php.net/manual/en/pdo.prepare.php

String interpolation always has the risk of code injection if your input is not sufficiently cleaned.

Removing the possibility to execute arbitrary code is the easier AND much safer way.

Answer 4

@Techpriester: that's not what a prepared statement is.

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html (old version, same thing)

PDO is a database abstraction layer that "prepares statements", but a prepared statement is something entirely different!