Parametrized Queries - Why are they not popular?

[bachx]

I was searching a bit on how to properly secure user input/queries then I stumbled upon parametrized queries, people recommended them as they were basically immune to SQL attacks while being a bit faster than the regular mysql_query function.

 

All well in theory. My question is, why these are not popular? I only read about them by chance and it doesn't seem to be used widely. Are there any hidden drawbacks for using parametrized queries? Should I simply keep using mysql_query and filter user input manually?

 

I'm starting a new project so I would appreciate any input on this.

 

 

[JasonLewis]

Having never heard of them, I can only say that most people these days either opt for MySQLi or using a database abstraction layer such as PDO. Both of these offer what is called prepared statements. Of course, that link is for PDO, but MySQLi offers the same thing.

 

Most people these days tend to use these methods for preventing SQL Injection.

[bachx]

I actually meant PDO/Prepared statements (They were called parametrized queries when I looked them up).

 

The basic syntax is something like this:

$sth = $pdo->prepare('SELECT * FROM members WHERE username = ? AND password = sha1(?)');
$sth->bindValue(1, $_POST['myusername'], PDO::PARAM_STR);
$sth->bindValue(2, $_POST['mypassword'], PDO::PARAM_STR);
$sth->execute();

 

My main question is, are there any drawbacks of using it? Given it's advantages it's not as widely used as it should.

[JasonLewis]

You might need to write a few extra lines, but in the long run it could save you lines. It also removes any overhead from creating multiple queries, because it allows you to bind more params to the statement then execute it again. Most people tend to just use MySQL, however I would recommend switching to at least MySQLi, but I myself prefer to use PDO, as it gives you the added functionality of switching to a different database driver without having to go through and change up all your code.

[bachx]

Thanks for the detailed answer, looks like I'll opt for PDO.

 

However, I have two last question:

 

- How is it compared to MySQL speed wise? I plan on using it for an online text based game, so there will be lots of DB interaction, is PDO optimal for that?

- What is the equivalent to mysql_fetch_array/mysql_fetch_object, in case I want to loop through the results?

 

That's all, thanks.

[bachx]

Bump for the questions in my last post.

[wildteen88]

so there will be lots of DB interaction, is PDO optimal for that?

This will all be depended on how efficient your code is and making sure your queries are optimised.

 

- What is the equivalent to mysql_fetch_array/mysql_fetch_object, in case I want to loop through the results?

Have a read off the PDO function list in the manual

http://php.net/pdo

 

However if you're using a MySQL database then it is best to use the MySQL Improved extension.