Prepared/parameterized queries, are they really the same thing?

MySQL database
#1

All over the internet you can find posts and tutorials saying these are two terms for the “exact same thing”.

In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency.
Both parameterized queries and prepared statements are exactly the same thing. Prepared statement seems to be the more commonly used term, but there is no difference between both terms.

But is it really?

[php]$sth = $dbh->prepare('SELECT * FROM user WHERE id = ’ . $id);[/php]

This will be prepared and cached by the SQL server as ie “SELECT * FROM user WHERE id = ‘313’”. While it’s really useless to prepare a query like this - it’s still a prepared statement!

But we can all agree this is not what we mean when we say “use prepared statements” - so what do we mean? Generally we do not really consider (too much) the i[/i] performance gain on preparing statements.

We usually front it to combat SQL injection - hence what we’re really after are “parameterized queries”.

[hr]

Am I the only one who don’t think these are the same at all, and get frustrated as beginners might think they’re all good if they just prepare their unsafe query? Quite open to the possibility that I might be :stuck_out_tongue:

I just know this is a problem that should have been solved ~10 years ago, we should make it as easy as possible to understand this stuff.

#2

I think you need to look at it in terms of the database back end you’re using.

In MySQL - a prepared statement is put in a special cached, usable for that particular session.

In Oracle - They have a share memory pool buffer of X size that can store X sql statements, when statements are parametized - there are less statements in the memory pool and they don’t get re-parsed. Therefore, they act as prepared statements in the MySQL world.

Take a look at the attached Oracle Chart.

oracle.png

#3

My real concern is that people who are new to this is told to “use prepared statements to avoid sql injection” - whrn prepared statements doesn’t help resolve that issue at all

#4

I agree, when people say “use prepared statements to avoid sql injection”, the should be saying “use prepared statements with Parameterized Queries to help prevent SQL injection.”

#5

I know the full statement but it just takes too long to say and type, especially when you have to constantly tell mysql_* noobs over and over again so I just say PDO prepared statements.

#6

Well, technically you don’t have to say it all. “Use PDO with parameterized queries” should be enough, as you can’t use parameters without preparing - though you can prepare without using parameters".

An example just popped up on stack overflow of someone who has heard “use PDO”:

#7

I’d argue that the example is simply an incorrectly used prepared statement. The code doesn’t make sense for any meaning of the word, so it’s a code problem, not a terminology problem.

I do agree that the term parameterized query is slightly clearer. I also agree that many performance-oriented explanations of prepared statements miss the point. On the other hand, “prepared statement” is much more common and less likely to cause confusion, especially since it reflects the actual method name. And the kind of people who fudge up prepared statements will hardly understand the subtle semantic differences between the two terms.

This is why I usually go with “prepared statement” and show people how it works.

#8

Execute a prepared statement with named placeholders or Execute a prepared statement with question mark placeholders I think would clarify the intent. After all it’s the placeholders that is important, for without that you’re vulnerable to SQL Injection.

I don’t know if PHP 7.0 addresses this problem:
[php] // Constructor - Build the PDO Connection:
public function __construct() {
$db_options = array(
/* important! use actual prepared statements (default: emulate prepared statements) /
PDO::ATTR_EMULATE_PREPARES => false
/ throw exceptions on errors (default: stay silent) /
, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
/ fetch associative arrays (default: mixed arrays) */
, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$this->_connection = new PDO(‘mysql:host=’ . DATABASE_HOST . ‘;dbname=’ . DATABASE_NAME . ‘;charset=utf8’, DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
}[/php]
Specifically PDO::ATTR_EMULATE_PREPARES => false, which can lead to false since of protection if it isn’t set to false.

Sponsor our Newsletter | Privacy Policy | Terms of Service