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

First off, what this is about is called is SQL-Injection. It's basically just the possibility to alter queries against the database via user input.

Let's look at an 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 usage within a query.

But in the end, you should stop using the mysql_* altogether. They're deprecated and considered as insecure when it comes to preventing SQL injection or other means of tempering with the queries.

You should simply stop concatenating queries together like this and start using prepared statements, which not only are easier to use, prevent SQL Injection by default but also can improve the speed of your application.

For PHP there are two extensions which are designed to close the whole mysql_* opened:

• mysqli
• PDO

And I say it again: Please stop using mysql_*!

Answer 2

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 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.