Use of parameters for mysql_query

Question

somewhere while studying I juz found out something interesting.. It says something as follows:

$query = sprintf("SELECT firstname, lastname, address, age FROM friends 
WHERE firstname='%s' AND lastname='%s'",mysql_real_escape_string($firstname),
    mysql_real_escape_string($lastname));

using the query like this instead of

$query="select firstname, lastname, address, age FROM friends
WHERE firstname='".$_RETURN['name1']."', lastname='".$_RETURN['name2']."'";

does this seem reasonable.. have u tried this coding ever.. and how it helps prevent any malicious attacks..

Answer 1

As far as I'm aware, mysql_real_escape_string is one of the better ways to prevent SQL injection, short of using prepared statements with mysqli or PDO.

Answer 2

Example:

Query:

SELECT temp1 FROM temp WHERE temp2 = 'VAR1'

Now we'll assign VAR1 the value of: '; DROP TABLE *; -- And we'll get:

SELECT temp1 FROM temp WHERE temp2 = ''; DROP TABLE *; --'

With mysql_real_escape_string it would look like this:

SELECT temp1 FROM temp WHERE temp2 = '\'; DROP TABLE *; --'

mysql_real_escape_string 'secures' a string for useage within a query.

Answer 3

Using formatting functions like sprintf is purely a matter of taste; the big advantage in the first example is that the function mysql_real_escape_string prevents all SQL injections (explained in one of the other answers); unlike the somewhat iffy magic_quotes_gpc feature in PHP, which many people rely on instead.

magic_quotes_gpc automatically escapes things you receive in requests from clients... but it cannot detect so-called second-level injections:

1. You get a malicious query from a client and store its contents in the database. magic_quotes_gpc prevents SQL injection; the malicious string gets stored correctly.
2. Later on, you fetch this string from the database and include it in another query. Now the string didn't come out of a request, so magic_quotes_gpc doesn't escape the string. Voilà, SQL injection; your data is now probably gone.

Using some means of escaping yourself, either something like mysql_real_escape_string or a database abstraction layer with a query builder (e.g. Adodb), is definitely superior to just hoping for the best.