2

Like my title, I want to know how to create a prepared statement without using MySQLi or PDO. The main point is learning the process of creation and it's security. I have nearly "ZERO-KNOWLEDGE" in this. Tried googling the topic but it seems that my googling skills have failed me. I've blindly learned that prepared statement is pretty secure from some websites but I do not see the actual point that it is secure. Can some of you clarify why it is? That would be a big help. I have my own assumption that creating a prepared statement is simply creating a function that receive string and replace all special character with str_replace like:

$org_sql = 'SELECT * FROM `my_table` WHERE `table_id`=?';
$prepared_sql = replaceQueryString($org_sql, 10);

function replaceQueryString($str, $replace) {
    // Do the replace where ? will be replaced by $replace here.
    return $str;
}

But I wonder if that will raise the level of the security, it looks rather plain to me.

If you don't want to waste time answering this just point me to where the resource is available. So in summarize.

  1. How to create a MySQL prepared statement?
  2. Why it is secure?

Many thanks.

Mysteltainn
  • 421
  • 3
  • 5
  • 13
  • 2
    Prepared statements are not just about escaping things. In particular, a true prepared statement would be compiled by the database, for use with inputs to be provided. – Waleed Khan Aug 28 '12 at 02:30

2 Answers2

5

Prepared statements are created and executed by the database, not by your code. To implement them yourself, you would have to implement the MySQL protocol and talk to the database directly.

Here's how prepared statements work:

  • You send the query, with placeholders, to the RDBMS
  • The RDBMS stores the query and pre-computes an execution plan
  • You separately tell the database to execute the statement, and pass the values to bind to its parameters
  • The RDBMS executes its stored plan using the values
Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • 1
    This means that I cannot have real prepared statement using MySQL and not MySQLi right? Because I remembered that I've read some article that say MySQL does not support prepared statement – Mysteltainn Aug 28 '12 at 03:02
2

There are two types of prepared statements, the emulated prepared statements and the native prepared statements. What you are doing is emulating the prepared statements.

1.How to create a MySQL prepared statement?

In php level, you could only emulate the prepared statement by replacing the placeholder with secure values. (Ex: quote the string, escape the special char, and so on...)

2.Why it is secure?

Because the prepared statements prevent the sql injection.

PS: PDO has the option of PDO::ATTR_EMULATE_PREPARES, which enables or disables emulation of prepared statements.

xdazz
  • 158,678
  • 38
  • 247
  • 274
  • Thanks for answering @xdazz. Anyway, I'm trying not to use PDO or MySQLi here due to some restrictions. MySQLi comes with PHP5 right? What about PDO? and in your PS PDO::ATTR_EMULATE_PREPARES is an emulation of prepared statement. Does that mean that it is less secure than the real one? – Mysteltainn Aug 28 '12 at 03:07
  • @user1478598 PDO also comes from PHP5. `PDO::ATTR_EMULATE_PREPARES` does not effect the security problem. – xdazz Aug 28 '12 at 03:11
  • To be straight, My webserver seems to have an old version of php, so it would be dangerous to implement such new things and I have no privileges to upgrade it. – Mysteltainn Aug 28 '12 at 03:11
  • @user1478598 For old version of php, you could use something like `PEAR::MDB2` etc. – xdazz Aug 28 '12 at 03:13
  • Understood. Thank you very much. It looks like that I have to stick with the old style. – Mysteltainn Aug 28 '12 at 03:13
  • What is the PEAR::MDB2? Is other DBMS? If it is I can't because I can't change anything on the webserver I'm working with. – Mysteltainn Aug 28 '12 at 03:15
  • @user1478598 It is a db abstraction layer. http://pear.php.net/package/MDB2/redirected – xdazz Aug 28 '12 at 03:19