PDO Prepare

[pwntastic]

Hello, 

 

I am pretty new to PDO but have heard that it is good to use prepared statements to help avoid mysql injections.  

 

What I'm wondering is, when using prepare, does one need to bind parameters or would one be able to do something like the following without risking security? 

$db = new PDO(..);

$r = $db->prepare("SELECT * FROM test WHERE col=$_POST['col']");
$r->execute();

Thanks

[deathbeam]

I am always using PDO bind when using prepary (actually, If you will not use bind and pass value directly to prepary it will throw exception).

[mac_gyver]

the main point of using a prepared query is to bind data into the syntax of the query. the syntax of the query, less the data, is what is being prepared. the data values are actually supplied and inserted when the query is ran, so that there's no possibility of sql injection.

 

by putting a variable holding raw data into the sql query statement, you have side-stepped the process, and allowed sql injection.

 

 

@deathbeam, putting a variable directly into the query being prepared, in itself, doesn't cause any type of error. in the php context, the sql statement is only a php string that is being built.

[Richard_Grant]

the main point of using a prepared query is to bind data into the syntax of the query. the syntax of the query, less the data, is what is being prepared. the data values are actually supplied and inserted when the query is ran, so that there's no possibility of sql injection.

 

by putting a variable holding raw data into the sql query statement, you have side-stepped the process, and allowed sql injection.

 

 

@deathbeam, putting a variable directly into the query being prepared, in itself, doesn't cause any type of error. in the php context, the sql statement is only a php string that is being built.

 

 

Not necessarily, i wouldn't say 'A VARIABLE' i would say a $_POST or $_GET, you can put any other variable that the user has no influence on without making a security risk.

[ginerjm]

I disagree Mr. Grant.  The ENTIRE purpose of preparing queries is to enforce all matters of security in that query.  Regardless of what type of var it is.  Too many times people can forget to do what is supposed to be done - hence a prepared query.  Whether it is a local var or a post or get or request var it should NOT be in the query text itself.  It should always be provided via a substitute parm, ie ? or :parm.

[mac_gyver]

prepared queries aren't just about security.

 

$variable = "Mc'Gyver"; results in broken sql syntax and an error.

[pwntastic]

I see.

 

Bind I shall then.

 

Thanks everyone!

[DavidAM]

Hello,

 

I am pretty new to PDO but have heard that it is good to use prepared statements to help avoid mysql injections.

 

What I'm wondering is, when using prepare, does one need to bind parameters or would one be able to do something like the following without risking security?

$db = new PDO(..);

$r = $db->prepare("SELECT * FROM test WHERE col=$_POST['col']");
$r->execute();

Thanks

 

@pwntastic I'm glad you asked the question. The short answer is: no, only the bound data (none in your example) is protected from SQL injection.

 

 

DavidAM climbs up on his soap-box

 

 

Prepared statements do NOT automatically protect you from SQL injections. And I wish people would quit suggesting them for that purpose. If we are going to suggest that, we may as well suggest turning MAGIC QUOTES back on!! It amounts to the same thing -- a false sense of security. The proof is in the OP's original question:

 

Prepared statements are not about security. They were invented - years and years ago - for the purpose of improving performance on queries run in a loop. There are two stages of execution at the database server for a query. 1) Build the query execution plan; and 2) Execute the plan (with specific data). When it was necessary to execute a query in a loop, the "plan" for that query was built every time. Prepared statements were invented so the plan could be built once, and executed multiple times.

 

I am NOT advocating queries in a loop. The RDBMS is built on Set Theory, and it will always be more efficient to execute a single query to affect multiple rows, when possible. But sometimes it is necessary to use a loop to execute the same query, with the calculations for the data being too complex or not available to put in a single query statement. For this we have Prepared Statements.

 

Read the manual:

 

 

The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

http://us1.php.net/manual/en/mysqli.quickstart.prepared-statements.php (emphasis added)

 

 

 

The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.

http://us1.php.net/manual/en/pdo.prepared-statements.php (emphasis added)

 

When true server-side prepared statements are used, it takes two round-trips to the database. When a non-prepared statement is executed, it takes only one round-trip. Using prepared statements exclusively increases resource use.

 

The PDO page also says:

 

The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

(emphasis added)

 

The automatic quoting of the bound data is a side-affect of the prepared statement. And we need to clearly state that only the bound data is protected from SQL injection. Otherwise, we are giving a false sense of security; the same false sense of security that many PHP coders had when using magic_quotes - which has been removed from the language because of the false sense of security.

 

 

DavidAM climbs off his soap-box

[Strider64]

 

The PDO page also says:

 

(emphasis added)

 

The automatic quoting of the bound data is a side-affect of the prepared statement. And we need to clearly state that only the bound data is protected from SQL injection. Otherwise, we are giving a false sense of security; the same false sense of security that many PHP coders had when using magic_quotes - which has been removed from the language because of the false sense of security.

 

 

DavidAM climbs off his soap-box

 

Not trying to make it more confusing as it is, but isn't that exactly what the OP has, by that I mean the data ISN"T bound so the data must be protected some other way from SQL injection. I remember reading a book by Larry Ullman on PHP and in the example he gave he didn't use prepared statements for it was just a simple little query statement, but he made sure to secure it just the same. 

[kicken]

Occasionally new developers have a misconception that by simply using the prepare() method they are somehow magically protected from SQL injection. This conception is someone re-inforced when people say "Use prepared queries to prevent injection". The act of preparing a query does nothing for protecting you from injection. It is using bound parameters that will offer you the protection. As such, it'd be more accurate to say "Use parameterized queries to prevent injection". In order to use parameters you must first prepare the query though, which is why some people will use the terms interchangeably when they really shouldn't.

[pwntastic]

All this brings up another question, is binding really better than using something like mysqli_real_escape_string?

[ginerjm]

according to some here doing a prepare and then parameterized values is the best.  You don't have to actually do the bind.  The manual shows that you can do this:

 

$q = "select fld1,fld2 from table where key=:keyval and blah=:blah and blah2 = :blah2";
$qst = $pdo->prepare($q);
$qst->execute(array('keyval'=>'mykey','blah'=>'blahval1', 'blah2'=> 'blahval2'));
if (!$qst)
    (handle error)
while ($row=$qst->fetch())
     echo $row['fld1']."  ".$row['fld2']."<br>";

[Jacques1]

By all means, do use prepared statements. Yes, they are much more reliable than manual escaping (if used correctly), and, yes, they are a fantastic security feature.

 

Don't let DavidAM's strange rant confuse you. I have no idea what he's trying to tell us. Whether or not the historical purpose of prepared statements was to improve the performance of repeated queries is entirely irrelevant here. The point is this:

 

All values you pass to the parameters of a prepared statements cannot interfere with the query. This reliably prevents SQL injection attacks as well as unintentional errors.

.

Yes, you do have to pass the values to the parameters. If you circumvent the entire mechanism, then of course it cannot work. That doesn't mean it creates a “false sense of security”. Every tool eventually breaks if we don't use it correctly.

 

I strongly advise against manual escaping with mysqli_real_escape_string(). In theory, it may work well. But in practice, it's far too fragile:

• Even experienced developers constantly forget to escape values. Just read the security announcements of any big PHP project.
• Many people think it's smart to only escape the values which they find “dangerous” (see Richard's post). This again leads to bugs or even security vulnerabilities.
• Escaping is vulnerable to character encoding issues. For example, many programmers use a SET NAMES query to change the encoding of the database connection. This is very, very wrong, because it doesn't update the encoding information within the MySQL API. As a result, mysqli_real_escape_string() still uses the original encoding and may cease to function entirely.

Prepared statements are much more robust. You just have to understand the concept: Stop injecting variables into query string. Use one static query template with parameters, and then pass your values to those parameters. This eliminates any risk of those values causing trouble.

 

Note that there's a pitfall in the PDO extension: By default, PDO does not use prepared statements. It just escapes the values and literally inserts them into the query string, which is effectively the same as using mysqli_real_escape_string(). To get actual prepared statements, you have to explicitly set PDO::ATTR_EMULATE_PREPARES to false.

Edited September 10, 2014 by Jacques1

[ginerjm]

Pwntastic:  The above is THE Answer you've been looking for.  Jacques1 has spoken .

[Richard_Grant]

I disagree Mr. Grant.  The ENTIRE purpose of preparing queries is to enforce all matters of security in that query.  Regardless of what type of var it is.  Too many times people can forget to do what is supposed to be done - hence a prepared query.  Whether it is a local var or a post or get or request var it should NOT be in the query text itself.  It should always be provided via a substitute parm, ie ? or :parm.

 

From my experience you can only bind a variable on the right side of a value. You can no bind table names or col names, correct me if i am wrong though because i was having issues trying to bind table name and cols.

[jazzman1]

@David, what did you mean by saying "improving performance on queries run in a loop"? Prepared and executed statements have nothing to do with application languages such as php, for instance. All prepared and executed statements occurred in database server, what PDO did is just collecting them, so they can be thought like a sql template with collection of sqls.

 

But, if you meant by saying this on a database level, yes, you are correct. For example, prior firebird 2.5 version, the execute statement occurred in a loop was prepared, executed and released upon every iteration, which wasn't good. Anyways.... like the mac_gyver's post #3 especially the first paragraph

Edited September 11, 2014 by jazzman1

[kicken]

@David, what did you mean by saying "improving performance on queries run in a loop"? ...

It reduces the work that the database server has to do. Normally when you run a query the server has to go through a number of steps, namely

- Parse the query text into it's components

- Examine the the query and optimize it if possible

- Develop an execution plan for how it will actually process the query

- Execute the execution plan

 

When you prepare the query, it performs the first three steps, but not the last. When you call the execute method it performs the last step with the appropriate values for the parameters. So if you need to run the same query a number of times in a loop with just different parameters, by preparing the query prior to the loop rather than within the loop you can save the database server some work by not requiring it to run the first three steps on each loop iteration.

 

From my experience you can only bind a variable on the right side of a value. You can no bind table names or col names, correct me if i am wrong though because i was having issues trying to bind table name and cols.

You are correct, you can only bind values in a query, not identifiers (like table/column names) or syntax items (like a JOIN clause). One other common misconception is that you can do something like ... IN (:param) and have :param bound to an array or comma-separated list. This does not work, you have to use a separate parameter for each possible value in the in clause.

[jazzman1]

 

It reduces the work that the database server has to do.....

Absolutely! Using prepared statements will guarantee, that even though they could be executed multiple times (as in your steps example) they are compiled and optimized only once by the database engine. We should always be using prepared statements in our application. 

[Jacques1]

PDO always uses prepared statements internally, even if we call the query() method. So it's not like we had a choice.

 

See the source code: PDO::query() calls the mysql_handle_preparer() C function. And this function creates a prepared statement.