Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 1 of 1
  1. #1
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    PHP and MySQL are continuosly developing. Features that until recently could only be found in high end commercial products, are now becoming available in the opensource environment.

    In this three part tutorial, I will give a quick introduction to three of the new features available in PHP5 and MySQL (4.1.x and 5.x.x), of which two belong to the previous mentioned category. The third is a newly developed ‘flat-file’ version of SQL called SQLite, that comes bundled with PHP 5.

    In this first tutorial, we’ll have a look at prepared statements, what they are and why/how you could use them.


    Prepared statements:

    The title really says it all.
    Prepared statements are SQL queries that are prewritten and precompiled at the DB server, only requiring variable inputs to execute. The advantages of this as opposed to passing queries as strings, are quite a few.

    When sending a query the normal way, the client (script) converts the data into strings that are then passed to the DB server. The DB server then uses CPU power to convert them back into the proper binary datatype. The database engine then parses the statement and looks for syntax errors. Once the statement is parsed, the database needs to figure out the most efficient way to execute the statement. The process involves finding indexes that might help the query, whether it needs to do a full read of all rows, etc. Once a query plan is created, the query is executed.

    When using prepared statements, the transfer protocol has been altered, and the data are sent in a native binary form, which saves the conversion-CPU-usage, and makes the data transfer more efficient. Obviously, this will also reduce bandwidth usage if the client is not co-located with the DB server.

    When doing multiple tasks requiring the same type of query (like inserting multiple rows), the DB can reuse its ‘query-gameplan’, saving precious time and CPU usage.
    (As of now, Mysql (4.1.x) does not cache the execution plan. This will come in later upgrades, and hence reduce the execution time further. The advantage of only parsing the query once, is though incorporated)

    Another advantage of prepared statements is security. A normal problem when dealing with user defined inputs, is so called SQL-injections.
    Normally, a query requiring user input is restricted to a variable or two in the query (which would be defined in the script). However, by cleverly entering data in the submit field, you could alter the query itself. Trouble characters are single and double quotes, and backslashes. If your script does not escape those before committing them to the query, a heapload of opportunities opens up. You can insert redirects, wildcards, default behaviours, etc. Even when adhering to the ‘escape rules’ (addslashes), there are hacker work-arounds.
    When using prepared statements, this is unnecessary. The variable types are predefined, and hence MySQL take into account these characters, and they do not need to be escaped.

    There are some limits to using prepared statements. As of now, only INSERT, REPLACE, UPDATE, DELETE, CREATE TABLE, and SELECT type queries, are supported.

    There are however some downsides. In prepared statements, the ‘pre-query’ is sent to the server for processing. The parsed syntax is checked for errors, and an OK message is sent back to the client. Variables are then sent and processed, and results are sent back. If your query is of a ‘single request’ type, then it will actually take longer to execute. The trade off in these cases will be security versus speed. In repetitive, complicated queries, the speed benefit will be tangible in high-traffic sites.

    Well, enough theory. How do you actually write them?

    In PHP, you have to use the mysqli-extension to gain access to these new and advanced MySQL features.

    There are two types of prepared statements:
    One that manipulates data, and one that retrieves data. Prepared statements allow you to bind PHP variables directly for input and output.
    After MySQL has received the prepared statement, it validates the statement as well-formed and meaningful, stores it in a special buffer, and then returns a handle that can be used to reference the prepared statement.

    The order of events are as follows:

    Input variables:
    • parsing the statement
    • binding input variables
    • assigning values to bound variables
    • executing the statement

    Output variables:
    • parsing the statement
    • executing prepared statement
    • binding output variables
    • fetching data into output variables

    There are (as always) two approaches, the procedural way and the object oriented way. In this example I’ll use the object oriented, as it’s less verbose and utilizes prebuilt classes in PHP.

    Input example:
    PHP Code:
    $con mysqli_connect($host$user$password$database) or die(mysqli_connect_error());

    $con->query("CREATE TABLE WDF_demo(
      `id` int(11) NOT NULL auto_increment,
      title varchar(30) NOT NULL default '',
      content varchar(30) NOT NULL default '',
      PRIMARY KEY  (`id`)
    ) "
    ) or die(mysqli_error($con));

    //The question marks denotes the reserved variable spaces
    $statement=$con->prepare("INSERT INTO WDF_demo VALUES(NULL, ?, ?)");

    $statement->bind_param("ss"$title$content);

    $title "Test 1";
    $content "This is test one";
    $statement->execute();

    $title "Test 2";
    $content "This is test two";
    $statement->execute();

    $title "Test 3";
    $content "This is test three";
    $statement->execute();

    $title "Test 4";
    $content "This is test four";
    $statement->execute(); 
    As you can see in the bind_param statement, we define the type of variables. In this case both variables are strings, or ‘s’.
    We can define 4 types of variables:
    i = integer
    d = double (a decimal number)
    s = string
    b = BLOB (which will be sent in packages)


    Output example:
    PHP Code:
    $con mysqli_connect($host$user$password$database) or die(mysqli_connect_error());


    $statement=$con->prepare("SELECT title, content FROM WDF_demo ORDER BY id DESC");
    $statement->execute();

    $statement->bind_result($title$content);

    echo 
    "<table border=\"1\" cellpadding=\"5\">\n";
    echo 
    "<tr><th>Title</th><th>Content</th></tr>\n";

    while(
    $statement->fetch())
    {
        echo 
    "<tr><td>$title</td><td>$content</td></tr>\n";
    }
    echo 
    "</table>"
    S. Rosland

  2.  


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:59 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com