10 Replies - 1858 Views - Last Post: 20 May 2012 - 02:58 PM Rate Topic: -----

#1 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 10:40 AM

Hi, I am reading everywhere that unlike mysqli, mysql doesn't support prepared statements. But I wonder how's that. Prepared statement is making a query statement with some user input value, right? So if $value contains a value input by the user, we can just make a double-quoted statement like "Select * from table where value = $value", and passing this statement to mysql_query returns results just as expected. So what's the deal?

Is This A Good Question/Topic? 0
  • +

Replies To: Prepared statements with mysql_connect() just like mysqli

#2 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10180
  • View blog
  • Posts: 37,586
  • Joined: 27-December 08

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 12:33 PM

A Prepared Statement does a lot in the background that the mysql family of functions does not handle. Prepared Statements sanitize the inputs, guarding against SQL Injection. They are also cached on the SQL Server. So when new parameters are bound to the query, it is more efficient to run the query again. The mysql family of functions does not support parameter binding in this manner.
Was This Post Helpful? 1
  • +
  • -

#3 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 12:40 PM

Ok, perhaps the mysql family doesn't cache the results. But the sanitizing can be done by special PHP functions and regular expressions, right? That should prevent injection. So barring the caching part, and the extra tedious bit of coding required to sanitize the user input which comes by default in mysqli, is it wrong to say that mysql supports prepared statements just as mysqli?
Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10180
  • View blog
  • Posts: 37,586
  • Joined: 27-December 08

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 12:45 PM

The checks you can do with the mysql family of functions still do not guarantee that your data has been completely sanitized.

Quote

So barring the caching part, and the extra tedious bit of coding required to sanitize the user input which comes by default in mysqli, is it wrong to say that mysql supports prepared statements just as mysqli?

No, this is not correct. You are ignoring everything that makes a prepared statement. The mysql family of functions do not support prepared statements.
Was This Post Helpful? 0
  • +
  • -

#5 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 12:47 PM

View Postmacosxnerd101, on 20 May 2012 - 07:45 PM, said:

No, this is not correct. You are ignoring everything that makes a prepared statement. The mysql family of functions do not support prepared statements.


Okay then, please explain what is meant by prepared statement.
Was This Post Helpful? 0
  • +
  • -

#6 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10180
  • View blog
  • Posts: 37,586
  • Joined: 27-December 08

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 12:49 PM

I did already above. The Wikipedia page and PHP Documentation are good places for further reading.
Was This Post Helpful? 0
  • +
  • -

#7 GunnerInc  Icon User is online

  • "Hurry up and wait"
  • member icon




Reputation: 856
  • View blog
  • Posts: 2,246
  • Joined: 28-March 11

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 12:54 PM

First off, Prepared Statements are compiled to byte code ONCE so if you are going to use the same statement over and over you gain a bit of speed.

Second, it protects you a bit from injection, the compiler sanitizes the query for you. In this respect is is wise to use prepared statements.

Damn, :shuriken: :shuriken:

This post has been edited by GunnerInc: 20 May 2012 - 12:54 PM

Was This Post Helpful? 2
  • +
  • -

#8 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 12:56 PM

Yeah, they corroborate to what I said above, don't they? The script has some boilerplate query, like Select * from foo where value = '?', and then that is executed like $stmt->execute(array($val)), right? How different is that from Select * from foo where value = $val ?

View PostGunnerInc, on 20 May 2012 - 07:54 PM, said:

First off, Prepared Statements are compiled to byte code ONCE so if you are going to use the same statement over and over you gain a bit of speed.

Second, it protects you a bit from injection, the compiler sanitizes the query for you. In this respect is is wise to use prepared statements.

Damn, :shuriken: :shuriken:



Yeah of course, it is wiser and more efficient, but because of that can we say that mysql doesn't support prepared statement? Yes, it would require more coding to tie up the loose ends to sanitize user inputs, and it would take more time too, but it would ultimately achieve the same thing, right?
Was This Post Helpful? 0
  • +
  • -

#9 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10180
  • View blog
  • Posts: 37,586
  • Joined: 27-December 08

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 01:07 PM

It deals with parameter binding. A Prepared Statement is similar to a function in this manner. You can bind new parameters to the statement in a similar way as you invoke a method with a different set of parameters. Because the Prepared Statement is cached, it runs more efficiently the second time around.

Quote

Yeah of course, it is wiser and more efficient, but because of that can we say that mysql doesn't support prepared statement?

Because of everything a Prepared Statement does that the mysql family of functions do not support means that they do not support prepared statements. The documentation will confirm this.
Was This Post Helpful? 1
  • +
  • -

#10 cupidvogel  Icon User is offline

  • D.I.C Addict

Reputation: 31
  • View blog
  • Posts: 593
  • Joined: 25-November 10

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 01:09 PM

Okay. Thanks.
Was This Post Helpful? 0
  • +
  • -

#11 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2834
  • View blog
  • Posts: 9,740
  • Joined: 08-August 08

Re: Prepared statements with mysql_connect() just like mysqli

Posted 20 May 2012 - 02:58 PM

I'd like to add that computer scientist have for decades been working to separate data and code for both better organization/readability and security. That's why we have so many different file types, as well as prepared statements! If you use the (more modern) tools the way they were intended to be used then things will be much easier for you, your users, and anyone who has to maintain your code.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1