How to use TpFIBDataSet, TpFIBQuery and TpFIBTransaction FIBPlus components to connect with Firebird / Interebase database in Delphi XE4?

How to use TpFIBDataSet, TpFIBQuery and TpFIBTransaction FIBPlus components to connect with Firebird / Interebase database in Delphi XE4?

Following is the basic article on Firebird / Interbase database connectivity in Delphi XE4 using FIBPlus database components like TpFIBDataSet, TpFIBQuery and TpFIBTransaction. I will explain all these FIBPlus database components in detail. I have written a small article on TpFIBDatabase before this article. Please go through that before reading this one. Read FIBPlus TpFIBDatabase...

FIBPlus TpFIBQuery Component

An application works with a database by issuing SQL instructions. They are used to get and modify data\metadata. FIBPlus has a special TpFIBQuery component responsible for SQL operator execution. This robust, light and powerful component can perform any actions with the database. 

TpFIBQuery is very easy-to-use: just set the TpFIBDatabase component, fill in the SQL property and call any ExecQuery method (ExecQueryWP, ExecQueryWPS). 

NOTE: The tpFIBQuery is not a TDataset descendant, so it does not act in exactly the same way or exhibit the same methods / properties as you would expect to find in a dataset. 

The example below will show how to create TpFIBQuery dynamically at run-time and thus get data about clients.

 var sql: TpFIBQuery;

 sql := TpFIBQuery.Create(nil);

 with sql do

 try

 Database := db;

 Transaction := db.DefaultTransaction;

 SQL.Text := 'select first_name, last_name from customer';

 ExecQuery;

 while not Eof do begin

 Memo1.Lines.Add(

 FldByName['FIRST_NAME'].AsString+' '+

 FldByName['LASTST_NAME'].AsString);

 Next; end;

 sql.Close;

 finally

 sql.Free;

 end;

FIBPlus TpFIBDataSet component

The TpFIBDataSet component is responsible for work with datasets. It is based on the TpFIBQuery component and helps to cache selection results. TpFIBDataSet is a TDataSet descendant so it supports all TDataSet properties, events and methods.

TpFIBDataSet enables you to select, insert, update and delete data. All these operations are executed by TpFIBQuery components in TpFIBDataSet. 

To select data you set the SelectSQL property. It’s similar to setting the SQL property of the QSelect component (TpFIBQuery type). Define the InsertSQL.Text property to insert data, UpdateSQL.Text to update, DeleteSQL.Text to delete and RefreshSQL.Text to refresh the data. 

Here is a demo database employee.gdb (or .fdb for Firebird) to show how to write Select SQL and get a list of all employees. I will write all queries in InsertSQL, UpdateSQL, etc.

with pFIBDataSet1 do begin

 if Active then Close;

 SelectSQL.Text := 'select CUST_NO, CUSTOMER, CONTACT_FIRST, CONTACT_LAST from CUSTOMER';

 InsertSQL.Text := 'insert into CUSTOMER(CUST_NO, CUSTOMER, CONTACT_FIRST,                                           CONTACT_LAST )' + 

                              ' values (:CUST_NO, :CUSTOMER, :CONTACT_FIRST, :CONTACT_LAST)';

 UpdateSQL.Text := 'update CUSTOMER set CUSTOMER = :CUSTOMER, '+

                   'CONTACT_FIRST = :CONTACT_FIRST, CONTACT_LAST = :CONTACT_LAST '+

                   'where CUST_NO = :CUST_NO';

 DeleteSQL.Text := 'delete from CUSTOMER where CUST_NO = :CUST_NO';

 RefreshSQL.Text := 'select CUST_NO, CUSTOMER, CONTACT_FIRST, CONTACT_LAST '                                       + 'from CUSTOMER where CUST_NO = :CUST_NO';

 Open;

end;

To open TpFIBDataSet either execute Open/OpenWP methods or set the Active property to True. To close TpFIBDataSet call the Close method

FIBPlus TpFIBTransaction component

A transaction is an operation of database transfer from one consistent state to another. All operations with the dataset (data/metadata changes) are done in the context of a transaction. To understand special FIBPlus features completely you need to know about InterBase / FIBPlus transactions. 

All the changes done in the transaction can be either committed (in case there are no errors) by Commit or rolled back (Rollback). Besides these basic methods TpFIBTransaction has their context saving analogues: CommitRetaining and RollbackRetaining, i.e. on the client side, these will not close a TpFibQuery or TpFibDataset.

To start the transaction you should call the StartTransaction method or set the Active property to True. To commit the transaction call Commit/CommitRetaing, to roll it back - Rollback/RollbackRetaining. 

TpFIBQuery and TpFIBDataSet components have some properties which help to control transactions automatically. In particular they are: the TpFIBDataSet.AutoCommit property; the poStartTransaction parameter in TpFIBDataSet.Options; qoStartTransaction and qoCommitTransaction in TpFIBQuery.Options.

TpFIBTransaction has three basic transaction types: 

tpbDefault, 

tpbReadCommited, 

tpbRepeatableRead. 

At design time you can also create special types of your own in the TpFIBTransaction editor and use them as internal ones. Set the transaction type to set its 

parameters:

TpbDefault – parameters must be set in TRParams

tbpReadCommited – shows the ReadCommited isolation level

tbpRepeatableRead – shows the RepeatableRead isolation level 

How to use TpFIBDatabase FIBPlus Component to connect with Firebird database in Delphi XE4?

How to use TpFIBDatabase FIBPlus Component to connect with Firebird database in Delphi XE4?

TpFIBDatabase component is used to make database connectivity with Firebird database in Delphi. For using TpFIBDatabase component, you should have FIBPlus and Firebird installed on your system. I am using Delphi XE4, Firebird 2.5.2 and FIBPlus 7.5 to make database connection.

Connection parameters are typical for InterBase/Firebird server:

1) path to a database file;

2) user name and password;

3) user role;

4) charset;

5) dialect;

6) client library (gds32.dll for InterBase and fbclient.dll for Firebird).

To connect to a database you should call the Open method or set the Connected property to True. It’s also possible to use this code to connect to a database:

function Login(DataBase: TpFIBDatabase; dbpath, uname, upass, urole: string): Boolean;

begin

 if DataBase.Connected then DataBase.Connected := False; 

 with FDataBase.ConnectParams do begin

   UserName := uname;

   Password := upass;

   RoleName := urole;

 end;

 DataBase.DBName := dbpath;

 try DataBase.Connected := True;

 except

   on e: Exception do

   ShowMessage(e.Message);

 end;

 Result := DataBase.Connected;

end;

To close the connection either call the Close method or set the Connected property to False. 

You can also close all datasets and connected transactions at once:

procedure Logout(DataBase: TpFIBDatabase);

var i: Integer;

begin

  if not DataBase.Connected then

  Exit;

  for i := 0 to DataBase.TransactionCount - 1 do

    if TpFIBTransaction(DataBase.Transactions[i]).InTransaction then 

      TpFIBTransaction(DataBase.Transactions[i]).Rollback

 DataBase.CloseDataSets;

 DataBase.Close;

end;

HTML5 MENU Tag Redefined

HTML5 <menu> Tag Redefined

The <menu> tag provides an easy way to create menus on a web page. The HTML <menu> element was deprecated in HTML 4.01 but is redefined in HTML5. The HTML <menu> tag defines a list/menu of commands. The <menu> tag is used for context menus, toolbars and for listing form controls and commands.

<li> tag is used within the <menu> tag. For example:

Which pet do you own?

<menu>

 <li>Dog</li>

 <li>Cat</li>

</menu>

You can also place radio buttons and check boxes inside <menu> tags. Have a look at following simple example of <menu> tag

radiobuttons inside <menu> tag

<menu>

   <li><input type="radio" id="radDog" class="radPets" name="radDog"/>Dog</li>

   <li><input type="radio" id="radCat" class="radPets" name="radCat"/>Cat</li>

</menu>

checkboxes inside <menu> tag

<menu>

   <li><input type="checkbox" id="cbDog" class="cbPets" name="cbDog"/>Dog</li>

   <li><input type="checkbox" id="cbCat" class="cbPets" name="cbCat"/>Cat</li>

</menu>

<menu> tag is used instead of <ul> tag

Earlier we used to make an unordered list by using <ul> tag like this:

<ul class="toolbar">

  <li>New</li>

  <li>Open</li>

  <li>Save</li>

  <li>Quit</li>

</ul>

But as I earlier said, <menu> is redefined in HTML5. The <menu> element represents an unordered list of commands. It has a type attribute, which can be set to popup or toolbar.

<menu type="toolbar">

  <li>New</li>

  <li>Open</li>

  <li>Save</li>

  <li>Quit</li>

</menu>

Please note: The <menu> tag is valid within blockquote, body, button, center, dd, div, fieldset, form, iframe, li, noframes, noscript, object, td and th tags.

How to secure jQuery AJAX calls in PHP from hackers?

How to secure jQuery AJAX calls in PHP from hackers?

If you are making jQuery AJAX calls in your PHP website, please ensure that those jQuery AJAX calls are secure from website hackers. Your code should not be vulnerable to hackers. Below are some methods and steps which need to be taken to secure your jQuery AJAX calls to PHP files. I am writing this post because I had written a simple post "How to call PHP function from JavaScript function? Always use AJAX." without mentioning any security code. I got following comment on that post:

"Your code is very vulnerable. You're not filtering the $_POST variable at all. This opens yourself to HTML injection. A hacker could pwn your web site very quickly if you used this code. Careless examples like yours is exactly why so many web sites are hacked."

That's why this is my small attempt to make your jQuery AJAX calls secure. 

1. Use $_SERVER['HTTP_X_REQUESTED_WITH']: 

This is a basic check to see if the request is an Ajax request or not?

if(!empty($_SERVER['HTTP_X_REQUESTED_WITH']) &&       strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest') 
{
    //Request identified as ajax request
}

However you should never base your security on this check. It will eliminate direct accesses to the page if that is what you need.

2. Use $_SERVER['HTTP_REFERER']: 

if(@isset($_SERVER['HTTP_REFERER']) && $_SERVER['HTTP_REFERER']=="http://yourdomain/ajaxurl")
{
 //Request identified as ajax request
}

But not all browsers set it. So don't properly rely on it but yes, to some extent it can secure your webpage.

Nobody can AJAX your site from other domain, but always can connect and drieclty send http request, for example by cURL.

JavaScript running on another domain cannot access any page on your domain because this is a violation of the Same-Origin Policy. The attacker would need to exploit an XSS vulnerability in order to pull this off. In short you don't need to worry about this specific attack, just the same old attacks that affect every web application.

3. Generate Access Tokens

$token = md5(rand(1000,9999)); //you can use any encryption
$_SESSION['token'] = $token; //store it as session variable

You can create some token in cookies, that will be also seen from jquery request, but that solution can also be hacked.

4. Always check $_POST variables in your PHP file whether those are set or not? Whether there is valid value in $_POST or not before executing the actual PHP code.

Basic code snippet for securing your jQuery AJAX calls in PHP

Step-1 : Generate Token System For All Web-Service:

Generating Token :

<?php
  session_start();
  $token = md5(rand(1000,9999)); //you can use any encryption
  $_SESSION['token'] = $token; //store it as session variable
?>

Step-2 : Use it while sending ajax call:

var form_data = 
{
  data: $("#data").val(), //your data being sent with ajax
  token:'<?php echo $token; ?>', //used token here.
  is_ajax: 1
};

$.ajax({
  type: "POST",
  url: 'yourajax_url_here',
  data: form_data,
  success: function(response)
  {
    //do further
  }
});

Step-3 : NOW, Let's secure ajax handler PHP file with,

session_start(); 
if($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest') 
{
  //Request identified as ajax request

  if(@isset($_SERVER['HTTP_REFERER']) &&    $_SERVER['HTTP_REFERER']=="http://yourdomain/ajaxurl")
  {
   //HTTP_REFERER verification
    if($_POST['token'] == $_SESSION['token']) {
      //do your ajax task
      //don't forget to use sql injection prevention here.
    }
    else
   {
      header('Location: http://yourdomain.com');
    }
  }
  else 
  {
    header('Location: http://yourdomain.com');
  }
}
else 
{
  header('Location: http://yourdomain.com');
}

How to call PHP function from JavaScript function? Always use AJAX.

How to call PHP function from JavaScript function? Always use AJAX.

Recently, I was developing a web application in PHP. I get into the need of calling my PHP function from my Javascript function. This is the common thing when you are developing a web application in PHP and have to call a PHP code from Javascript to refresh only a certain portion of your web page with server results. Always use AJAX to achieve this functionality. Using AJAX you can call server side code / functions (your PHP code) from client side (Javascript). Below is the PHP and Javascript code snippet to illustrate this concept. 

This is very simple example on how to call server side functions of PHP from client browsers (Javascript)? In following example, I have a PHP file named myscript.php which has function named myfunction(). This function uses two $_POST variables and just echoes them. I have mydiv HTML div anywhere on my webpage which I want to refresh with the result which is returned from my PHP script. In my Javascript code, I am using AJAX to call my PHP script with parameters and POST method. The result which is getting returned, I am showing that in mydiv HTML div. 

Have a look at this very simple PHP AJAX example:

PHP code

<?php

myfunction();

function myfunction()

{

$myvar = $_POST['q']." how are you?";

$myvar2 = $_POST['z'];

echo $myvar."\n".$myvar2;

}

?>

HTML code

<div id="mydiv"></div>

Javascript code

var data =" hello world";

var data2=" hello all";

function run()

{

$.ajax(

{
                   url: 'myscript.php',

                data: {'q': data,'z':data2},

                   type: 'post',

                   success: function(output) 

                {

                          //alert(output);

                          document.getElementById("mydiv").innerHTML += output; //add output to div  

                }

}

          );

}