0

Documents really only mentions:

mysqli::prepare -- mysqli_prepare — Prepare an SQL statement for execution

I want to know what actually happens under the hood after calling mysqli_prepare() with a sql statement.

Could anyone elaborate with that and also why couldn't it be skipped? Couldn't this be done internally in mysqlite3?

A reference would be appreciated.

Griffin
  • 648
  • 7
  • 25
  • 2
    It tells the database to build a SQL statement with placeholders that will be filled in later with bind variables - perhaps [this](http://www.mysqltutorial.org/mysql-prepared-statement.aspx) might explain – Mark Baker Sep 11 '17 at 16:10
  • @MarkBaker Thanks. Why this stage exists? Why couldn't it be skipped from users point of view? I will edit the question with a following question. – Griffin Sep 11 '17 at 16:14
  • The documents says more in the description section: `Prepares the SQL query, and returns a statement handle to be used for further operations on the statement. The query must consist of a single SQL statement.` If you want more of the "under the hood", then you'd have to get the PHP source code. – Paul T. Sep 11 '17 at 16:14
  • @Griffin what do you mean by skipped? – Script47 Sep 11 '17 at 16:15
  • @Script47 I mean, what prevents it to be done internally by `mysqlite3` ? e.g. as part of `$statement->execute()`. – Griffin Sep 11 '17 at 16:16
  • 1
    The prepare stage exists because it sets up a part-parsed/compiled statement on the database that can be reused multiple times with different bind variable values.... you can bind and execute in one step, but prepare is a separate step to allow reuse – Mark Baker Sep 11 '17 at 16:17
  • @MarkBaker The end goal of the user is to execute its statement. Wouldn't it make more sense just to give the statement to `execute()` and handle re-usability of its resources internally by associating to a handle? – Griffin Sep 11 '17 at 16:22
  • 1
    No! It might be as simple as that for some users but not for all.... there are users that do repeat statement execution with different bind vars, because it's better performance to do so.... if the statement had to be re-prepared every time, that performance boost would be lost – Mark Baker Sep 11 '17 at 16:29
  • 1
    Of course, there's nothing to prevent you writing a small function that will do all three steps in one go – Mark Baker Sep 11 '17 at 16:31

2 Answers2

1

The difference between execute() and prepare() is not that the prepare() method has some internal flag like "secure=true;" which will be set and magically you are save for SQL injections. In fact, you can have SQL injections with prepare() as well when you use it wrong:

$stmt = $db->prepare('SELECT password FROM user WHERE username = "'.$username.'"');
$stmt->execute();
// ...

The point of prepared statements is that the values/arguments/variables for the SQL query are send separated from the actual SQL query to the MySQL server. This way the values/arguments/variables cannot change the SQL query you are trying to send. This prevents SQL injections where the inputs contain values like "='' OR 1 = 1 --".

The prepared statement is building a data structure where you set the values for the prepared statement separated via the additional API calls like bind_param(). The SQL server will use the "prepared" statement and use the values received from bind_param(). The query has been read, analyzed, well, it has been "prepared", and is fixed now (for the duration of this prepared statement) before even the first value with the potential dangerous data has been read. The WHERE condition cannot be altered or weakened, another SQL query cannot be appended or other rows/columns/tables can be edited.

Because of this, you cannot just redirect the execute() call internally to prepare() to be safe, because then you are missing the bind_param() calls. That being said: Use execute() when you have a fixed SQL query without any variables and/or user inputs and use prepare() for prepared statements, where you have an SQL query which depends on variables and/or user inputs.

Progman
  • 13,123
  • 4
  • 28
  • 43
  • Thanks for the good points. "This way the values/arguments/variables cannot change the SQL query you are trying to send" seems like a clear point. – Griffin Sep 11 '17 at 16:57
0

In very short and in terms all will understand, you will want to use prepared statement(aka mysqli_prepare) to block sql injections, so in other words for security reasons.

user3744959
  • 47
  • 7