Tuesday, April 8, 2014

Delphi - Records

What are records?
Records are a useful and distinguishing feature of delphi. They provide a very neat way of having named data structures - groups of data fields. Unlike arrays, a record may contain different types of data.

Records are fixed in size - the definition of a record must contain fixed length fields. We are allowed to have strings, but either their length must be specified (for example a : String[20]), or a pointer to the string is stored in the record. In this case, the record cannot be used to write the string to a file. The TPoint type is an example of a record. Before we go any further, let us look at a simple example.
 type
   TCustomer = record
     name : string[30];
     age  : byte;
   end;
  

 var
   customer : TCustomer;
 

 begin
   // Set up our customer record
   customer.name := 'Fred Bloggs';
   customer.age  := 23;
 end;
When we define a record, each field is simply accessed by name, separated by a dot from the record variable name. This makes records almost self documenting, and certainly easy to understand.
Above, we have created one customer, and set up the customer record fields.
Using the with keyword
type
   TCustomer = record
     name : string[30];
     age  : byte;
   end;
 
 
 var
   John, Nancy : TCustomer;
 
 
 begin
   // Set up our customer records
   with John do
   begin
     name := 'John Moffatt';               // Only refer to the record fields
     age  := 67;
   end;
 
 
   with Nancy do
   begin
     name := 'Nancy Moffatt';              // Only refer to the record fields
     age  := 77;
   end;
 end;
A more complex example
In practice, records are often more complex. Additionally, we may also have a lot of them, and might store them in an array. The following example is a complete program that you may copy and paste into your Delphi product, making sure to follow the instructions at the start of the code.

Please note that this is quite a complex piece of code - it uses a procedure that takes a variable number of parameters, specially passed in square brackets (see Procedure for more on procedures).
// Full Unit code.
 // -----------------------------------------------------------
 // You must store this code in a unit called Unit1 with a form
 // called Form1 that has an OnCreate event called FormCreate.
 
 unit Unit1;
  

 interface
  

 uses
   Forms, Dialogs;
  

 type
   TForm1 = class(TForm)
     procedure FormCreate(Sender: TObject);
     procedure ShowCustomer(const fields: array of string);
   end;
 
 
 var
   Form1: TForm1;
 
 
 implementation
 {$R *.dfm} // Include form definitions
 
 
 procedure TForm1.FormCreate(Sender: TObject);
 type
   // Declare a customer record
   TCustomer = Record
     firstName : string[20];
     lastName  : string[20];
     address1  : string[100];
     address2  : string[100];
     address3  : string[100];
     city      : string[20];
     postCode  : string[8];
   end;
  

 var
   customers : array[1..3] of TCustomer;
   i : Integer;
 
 
 begin
   // Set up the first customer record
   with customers[1] do
   begin
     firstName := 'John';
     lastName  := 'Smith';
     address1  := '7 Park Drive';
     address2  := 'Branston';
     address3  := 'Grimworth';
     city      := 'Banmore';
     postCode  := 'BNM 1AB';
   end;
  

   // Set up the second and third by copying from the first
   customers[2] := customers[1];
   customers[3] := customers[1];
 
 
   // And then changing the first name to suit in each case
   customers[2].firstName := 'Sarah';
   customers[3].firstName := 'Henry';
  

   // Now show the details of these customers
   for i := 1 to 3 do
     with customers[i] do ShowCustomer([firstName,
                                        lastName,
                                        address1,
                                        address2,
                                        address3,
                                        city,
                                        postCode]);
 end;
  

 // A procedure that displays a variable number of strings
 procedure TForm1.ShowCustomer(const fields: array of string);
 var
   i : Integer;
 
 
 begin
   // Display all fields passed - note : arrays start at 0
   for i := 0 to Length(fields)-1 do
     ShowMessage(fields[i]);
 
 
   ShowMessage('');
 end;
  

 end.
Packing record data
By default, Delphi will pad out the record with fillers, where necessary, to make sure that fields are aligned on 2, 4 or 8 byte boundaries to improve performance. You can pack the data with the packed keyword to reduce the record size if this is more important than performance. See Packed for more on this topic.

Records with variant parts
Things get very interesting now. There are times when a fixed format record is not useful. First, we may wish to store data in the record in different ways. Second, we may want to store different types of data in a part of a record.
The Delphi TRect type illustrates the first concept. It is defined like this:
type 
   TRect = packed record
     case Integer of
       0: (Left, Top, Right, Bottom: Integer);
       1: (TopLeft, BottomRight: TPoint);
   end; 
Here we have a record that holds the 4 coordinates of a rectangle. The Case clause tells Delphi to map the two following sub-sections onto the same area (the end) of the record. These variant sections must always be at the end of a record. Note also that the case statement has no end statement. This is omitted because the record finishes at the same point anyway.
The record allows us to store data in two ways:
var
   rect1, rect2 : TRect;
 begin
   // Setting up using integer coordinates
   rect1.Left   := 11;
   rect1.Top    := 22;
   rect1.Right  := 33;
   rect1.Bottom := 44;
 

   // Seting up rect2 to have the same coordinates, but using points instead
   rect2.TopLeft     := Point(11,22);
   rect2.BottomRight := Point(33,44);
 end;
The TRect record showed two methods of reading from and writing to a record. The second concept is to have two or more record sub-sections that have different formats and lengths.
This time we will define a fruit record that has a different attribute section depending on whether the fruit is round or long:
type
   // Declare a fruit record using case to choose the
   // diameter of a round fruit, or length and height ohterwise.
   TFruit = Record
     name : string[20];
     Case isRound : Boolean of // Choose how to map the next section
       True  :
         (diameter : Single);  // Maps to same storage as length
       False :
         (length   : Single;   // Maps to same storage as diameter
          width    : Single);
   end;
 
 
 var
   apple, banana : TFruit;
 
 
 begin
   // Set up the apple as round, with appropriate dimensions
   apple.name     := 'Apple';
   apple.isRound  := True;
   apple.diameter := 3.2;
 
 
   // Set up the banana as long, with appropriate dimensions
   banana.name    := 'Banana';
   banana.isRound := False;
   banana.length  := 7.65;
   banana.width   := 1.3;
  

   // Let us display the fruit dimensions:
   if apple.isRound
   then ShowMessageFmt('Apple diameter = %f',[apple.diameter])
   else ShowMessageFmt('Apple width = %f , length = %f',
                       [apple.width, apple.length]);
   if banana.isRound
   then ShowMessageFmt('Banana diameter = %f',[banana.diameter])
   else ShowMessageFmt('Banana width = %f , length = %f',
                       [banana.width, banana.length]);
 end;
Note that the Case statement now defines a variable, isRound to hold the type of the variant section. This is very useful, and recommended in variable length subsections, as seen in the code above.

Developing Facebook Fan Page Requires Effort and Creativity

At this point of time, each and every individual is aware about Facebook. Promoting your products as well as services have become very necessary in today's business scenario and for that Facebook is one of the finest platforms. Either you must already be having a Facebook Fan Page for your business, or you must be planning to create a new one. Nowadays, it has become really very important to make use of these social networking sites to their fullest prospective. Moreover, setting up a Fan Page is not just the only activity; it must be customized attractively in order to achieve better results.

Facebook is one of the most substantial advertising platforms where you can create lot of businesses. You can also expand your business if you concentrate on Facebook Fan Page Development. The default Facebook page comes with very general features like the other pages and consists of wall, photos, basic info, videos and many more things. These are essential things that ought to be filled by the users in order to make an attractive page. These features will be visible to everyone who is registered on Facebook. If you want, you can also customize it as per your wish.

What Valid Specifications you need to install in Facebook page?
There is a specific segment on Facebook page which is referred as ‘tab'. Besides, every tab is available with some kind of particular feature. It may also be known as ‘widget or ‘module' as per the varying semantics. The generic features that are mentioned above, actually act as tab and serves the feature that it is built for.
Capable and talented Facebook application developers are free to develop wonderful customized tabs which are unique as well as applicable to the Facebook Fan page.

 The tabs may be designed and modified in such a manner that it will assist you to create an interactive user experience. More to the point, adding of these custom tabs will leave the Facebook page with much better and enhanced functionalities.

Expand your Business Opportunities with Facebook Fan Page development
You can very easily grow your business opportunities if the fan page is designed in a proper manner; thereby specifically depicting the business products and services. You ought to be well aware about the layouts of Facebook as well as the backgrounds of the profile. Even though, it is really very easy and simple to apply for any sort of layout, but it ought to be extremely creative to attract the attention of the visitors. Internet is the best solution to find the most apt layout for your business as per your needs and requirements.

It is significant to design Facebook Fan Pages by keeping in mind your business interests and hence maintaining the standard as well as quality is necessary. Adding of face apps also makes the page more and more attractive, and because of that the visitors keep visiting the page with supreme attention. The pictures or images of the products must be very clearly demonstrated and it must precisely be designed in order to attract more and more customers. The ideal size of the images is 200 x 400 pixels large and use of excessive images must be avoided. On the other hand, the page must be informative, speaking about the products and services the business is offering along with the contact information

 From : articlesbase.com

SQL Server - OLE DB Deprecated :(

As you may already know, the SQL Native Client OLE DB provider is being deprecated. This doesn't affect other OLE DB providers or the OLE DB API. Also, the SSIS OLE DB components are not being deprecated.

In SQL Server 2012 SSIS, you can continue to use OLE DB connections and will need to for components such as the Lookup transformation and the Slowly Changing Dimension transformation that require OLE DB. In the post-Denali release, you’ll be able to upgrade and continue to operate these packages without needing to do additional work to explicitly remove OLE DB from the packages.

SQL Server - Result paganation in sql server


A lot of SQL Server database users at some time faced a big obstacle, especially web sites programmers when need to read data from the database in case of a partial data was a fairly large size. After a long and arduous journey made in the search for the best method that can solve the problem very haunting, I've experimenting with dozens of ways that were not satisfactory at first, especially if the data is very large size.

In fact,i did't invent magic solution, but I found the best located after many modifications and tests, all web solutions was talked about using  these methods (ROW_NUMBER() , TOP Rows ,CTE and Temp tables) in sql server 2005 and older versions wich i prefer using TOP method to done this job , because it's flexible and no limitation or conflicts for using TOP with your query criteria's and orders data results .


Using TOP IN SQL Server 2005
i prefer using sql stored procedure with parameters like @page_num , @page_size and @query_type that i use @query type to switch between counting results and query reaults it self .
Create PROCEDURE [dbo].[GetArchive]
(@username NVARCHAR(100),@page_num int,@page_size int,@proc_type nvarchar(20))AS
BEGIN


declare @page_count int ,@StartRow int , @EndRow int ,@sql nvarchar(max)
 

if (@proc_type='get_count')
begin
 set @sql = 'SELECT Count(*) as AllCount FROM Archive where id > 0'


end
else
begin
 set @sql = 'SELECT TOP ' + cast(@page_size as nvarchar(100)) 
 set @sql = @sql + ' Name,Mobile,Email,[Datetime] '
 set @sql = @sql +' FROM Archive '
 set @sql = @sql + 'WHERE ID NOT IN(SELECT TOP ('+cast(@page_size as nvarchar(100))+' * ('+cast(@page_num as nvarchar(100))+' - 1)) ID FROM Archive where id > 0 AND Username ='''+@username+''' order by id desc) '
end
set @sql = @sql + ' and username = '''+@username +'''';


if (@proc_type<>'get_count')
   set @sql = @sql + ' order by id desc '




exec (@sql)

 

END
Executing procedure will generate sql query like this
SELECT TOP 10 Name,Mobile,Email,[Datetime] FROM Archive WHERE ID NOT IN(SELECT TOP (10 * (2 - 1)) ID FROM Archive where id > 0 AND Username ='User' order by id desc)  and username = 'User' order by id desc 
Paganation IN SQL Server 2012
Sql server 2012 intreduce new keyword to do this job in easy and fast way like any other databsaes especially LIMIT in Mysql, using OFFSET ended this long conflict by add this keyword to your query
SELECT
     *
FROM  [dbo].[Archive]
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY

Sunday, April 6, 2014

PHP -MySQLCrank



MySQLCrank

Provides a number of functions to make it easier to work with MySQL databases, without using a lot of mysqli_xxxx functions, making it easier to switch to a different database. Supports SELECT, INSERT, UPDATE, and DELETE queries only, first because of security reasons, second - other queries are just not really needed for web development. 

Using MySqli_ extension (as described in php.net):

The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later. The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:
  • Object-oriented interface
  • Support for Prepared Statements
  • Support for Multiple Statements
  • Support for Transactions

Supporting Parameters :

Through the support of the parameters you can use the various operations on the database in the same format, you can only change the type of operation required.

Classes

  • TDatabase
  • Includes all database operations function like SELECT , INSERT , UPDATE and DELETE .
  • TSQL
  • Includes structure of Parameters.

Setup database configuration

from TDatabase Class find these line and configure it as your database config.

$this->db_host "__SERVER_" ;$this->db_username "__DATABASE_USERNAME__";$this->db_password "__DATABASE_PASSWORD__";$this->db_database "__DATABASE_NAME__";?>

Basic Usage

Creating new instance of TDatabase class .

require_once(
"includes/TDatabase.php");$db = new TDatabase() ; /* Create new instance */
unset(
$db);  /* unset when finish using it. */
?>

Basic Select

Using Basic Select operation without any conditions.

$db = new TDatabase() ;$result $db->select("movies") ;
unset(
$db);?>

Select with where clause

Using Basic Select operation without any conditions

$db = new TDatabase() ;$db->where("movie_year","2013","i") ;$db->where("movie_rate","8","i") ;$result $db->select("movies") ;
unset(
$db);?>

where clause



/* where($name,$value,$type,$operand="=",$nextCondition="and") */
$db = new TDatabase() ;$db->where("movie_year","2013","i") ;$db->where("movie_rate","8","i") ;$result $db->select("movies") ;
unset(
$db);?>

Select with where and like operand



/* where($name,$value,$type,$operand="=",$nextCondition="and") */
$db = new TDatabase() ;$db->where("movie_name","Trek","s","like") ; $result $db->select("movies") ;
unset(
$db);?>

Select count for paging results



$db = new TDatabase() ;$db->where("movie_name","Trek","s","like") ;$resultCount $db->selectCount("movies") ; /* get count for same criterias*/
$result $db->select("movies") ;
unset(
$db);?>

Select and Limits



$db = new TDatabase() ;$db->limit(0,10);$result $db->select("movies") ;
unset(
$db);?>

Select and Order



$db = new TDatabase() ;$db->order("order by id desc");$result $db->select("movies") ;
unset(
$db);?>

Select with specified Columns

In case parameters added in select mode ,will be considered as columns must show only.

$db = new TDatabase() ;$db->addParameter("movie_rate","","i"); /* Parameters in select clause used as columns  */
$db->addParameter("movie_year","","i");$db->order("order by id desc");$result $db->select("movies") ;
unset(
$db);?>

Insert Operation



$db = new TDatabase() ;$db->addParameter("movie_name","Frozen (2013)","s");$db->addParameter("movie_rate","8","i");$db->addParameter("movie_year","2013","i");$db->addParameter("movie_story","Movie Story Here","s");$rows_affected $db->insert("movies") ;
unset(
$db);?>

Insert if not found and delete if found

An example of how easy to execute multi operations at the same time.

$db = new TDatabase() ;$db->addParameter("movie_name","Movie name","s");$db->addParameter("movie_rate","9","i");$db->addParameter("movie_year","2014","i");$db->where("movie_name","Movie name","s") ;

if(
$db->selectCount("movies")<=0)$db->insert("movies");
else
$db->delete("movies");

unset(
$db);?>

Update Operation with where



$db = new TDatabase() ;$db->addParameter("movie_rate","8","i");$db->addParameter("movie_year","2013","i");$db->where("id","1","i") ;$rows_affected $db->update("movies") ;
unset(
$db);?>

Delete Operation with where



$db = new TDatabase() ;$db->where("id","1","i") ; $rows_affected $db->delete("movies") ;
unset(
$db);?>

Safe Mode

You can activate safe mode to avoid overall effect on the records in operations (delete, modify) ,in this mode you must add one criteria at least .

$db = new TDatabase() ;$db->setSafeMode(true);$db->delete("movies") ;
unset(
$db);?>

Debug Query

You can see the query executed in database by calling $db->getLastQuery();

$db = new TDatabase() ;$db->select("movies") ;
echo  
$db->getLastQuery();
unset(
$db);?>

Download now ...

Saturday, April 5, 2014

SQL Server - Table Partitioning technique

Technical concept :
partitioning in simple word is spreads your data in multi file groups , file groups it's physical files on your Hard drive .

Usage:
This technique is used heavily in case of very large tables which are hard to get rid of this data on a regular basis, so it would be difficult to work out on the tables of millions of data that are used only when needed.

Therefore stems need to provide a mechanism to keep all the data available, while maintaining the good
performance of the database.
Partitioning a SQL Server database table is four-steps process:

1-Create the partition function
2-Create File Groups
3-Create the partition scheme
4-Partition the table


The remainder of this article explores each of those steps in further detail.
Step 1: Creating a Partition Function

partition defines the function how you want SQL Server to split the data. At this point, we are not concerned with any particular table, we select only the general technique to split the data.

Define partitions to determine the limits of each partitions .For example, suppose we have a customer table contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We may decide to split the table into four partitions equally spaced, using the following partition function (I call it customer_func):

CREATE PARTITION FUNCTION customer_func (int 
AS RANGE RIGHT 
FOR VALUES (250000, 500000, 750000)


These limits determine four partitions. The first contains all values ​​less than 250,000. The second contains values ​​between 250,000 and 499,999. The third contains values ​​between 500 000 and 749 999. All values ​​greater than or equal to 750,000 to go in the fourth section.

Note that I used the phrase "RANGE RIGHT" in this example. This indicates that the threshold value itself should go in the partition on the right side. Alternatively, if you have used the "LEFT RANGE", the first partition has included all values ​​less than [U] or equal to [/ U] 250,000; second section will be included values ​​between 250,001 and 500,000, and so on .

Step 2:Create File Groups
Create and defines 4 file groups .
ALTER DATABASE Customers
ADD FILEGROUP fg1;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg2;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg3;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg4;
GO

ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg1_dat,
    FILENAME = 'C:\Databases\Customersdb\fg1_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg1;

ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg2_dat,
    FILENAME = 'C:\Databases\Customersdb\fg2_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg2;


ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg3_dat,
    FILENAME = 'C:\Databases\Customersdb\fg3_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg3;


ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg4_dat,
    FILENAME = 'C:\Databases\Customersdb\fg4_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg4;
Step 3: Creating a Partition Scheme

Once you have a partition function describing [u]how[/u] you want to split your data, you need to create a partition scheme defining [u]where[/u] you want to partition it. This is a straightforward process that links partitions to filegroups. For example, if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:

 CREATE PARTITION SCHEME customer_partscheme

 AS PARTITION customer_func

 TO (fg1, fg2, fg3, fg4)


Notes that now link the partition function of the partition scheme, but we are still not linked to the partition scheme to any specific database table. Since the re-use of force comes into play. We can use this partition scheme (or simply a function of the division, and if we wish) on any number of database tables.

Step 4: Partitioning a Table

After defining the partition scheme, and you are now ready to create a partitioned table. This is the simplest step of the process. You can simply add the condition "ON" for the statement to create a table to determine the partition scheme and table column that applies to it. You do not need to determine the function of the partition because the partition scheme sets already.

For example, if you want to create the customer table using the partition scheme we have, and you use Transact-SQL statement following:

 CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)

 ON customer_partscheme (CustomerNumber)


That’s everything you need to know about partitioning tables in Microsoft SQL Server! Remember to leverage the power of reusability by writing generic partition functions and partition schemes that might apply to multiple tables!

Friday, April 4, 2014

PHP - Steps to increase the efficiency of PHP code


If you're a developer, it is necessary for you to improve your script in the development process itself early. Following the best practices while coding your PHP script is a good starting point to write PHP code optimization as well.

This tutorial provides few tips to optimize PHP code from a developer point of view.

1. Use Native PHP Functions
As much as possible, try to use the functions of the original PHP instead of writing your own functions to achieve your goal. For example, you can use range (b, k) to get of alphabets ranging from b to k in sequence, if necessary only once in the script instead of the Declaration of an array with the values ​​in the function and return the call.
2. Use Single Quotes
Using single quotes ( ‘ ‘ ) is faster than using double quotes( ” ” ) if you are going to keep only the string inside it avoiding any variables. Double quotes checks for the presence of variable and adds little bit of overhead.
3. Use = = = in your conditions
Use “= = =” instead of “= =”, as the former strictly checks for a closed range which makes it faster.
4. Use Appropriate Str Functions

str_replace is faster than preg_replace, but strtr is faster than str_replace by a factor of 4.
5. Calculate Only Once
Account and set the value to a variable if it is getting used many times instead of calculating it over and over again where it is used.
For example, the following will degrade the performance.
for( $i=0; i< count($arr); $i++){
  echo count($arr);
}
The script below will perform much better.
$len = count($arr);
for( $i=0; i< $len; $i++){
  echo $len;
}
6. Pass Reference to Function
Pass the reference to function if it does not affect your logic. The function of manipulating the signal faster than those that have been passed to manipulate the value as here is getting more and create a single copy of the value. Especially as it adds overhead when the value that was passed by you is a large group.
For example, let us create a function in two different way to increment by 1, each element of an array having values 0 to 99.
 
  // passing by reference
  function  computeValue( &$param ){
   // Something goes here
   foreach( $param as $k => $value){
     $param[$k] = $value + 1;
   }
  }
  $x = array();
  for( $i =0; $i<99; $i++){
    $x[$i] = $i;
  }
  computeValue( $x);
  
  // array with 100 elements each incremented by 1
  print_r( $x );

The function above works faster than the function below although both will produce the same result ( increment each element of the array by 1. )
 
   // passing by value
    function  computeValue( $param ){
      // Something goes here
      foreach( $param as $k => $value){
       $param[$k] = $value + 1;
      }
      
      return $param;
    }
    $x = array();
    for( $i =0; $i<99; $i++){
      $x[$i] = $i;
    }
 // array with 100 elements each incremented by 1
    print_r(computeValue( $x));
    
7. Create Classes Only When its Required
Don’t create classes and method until and unless its really needed, used and reused as well.
8. Disable Debugging Messages
File operations are expensive. So, if you have written lot of custom functions to log errors and warning during your development process, make sure you remove them before you push the code to production.
9. Use Caching Techniques
Use cache to reduce the load of database operations as well as the script compilation. We can use memcache for the reducing database load and APC for opcode caching and intermediate code optimization.
10. Close the Connection
Get into the habit to unset the variables and close database connection in your PHP code. It saves memory.
11. Reduce Number of Hits to DB
Try to reduce the number of hits to the database. Make queries aggregate so that you call the database less number of times. For example:
 
  $con=mysqli_connect("localhost","username","somepassword","anydb");
  
  if (mysqli_connect_errno())
  {
    echo "Failed to connect to MySQL" ;
 mysqli_connect_error(); 
  }

  function insertValue( $val ){
    mysqli_query($con,"INSERT INTO ttt (someInteger) VALUES ( $val )");
  }
  
  for( $i =0; $i<99; $i++){
    //  Calling function to execute query one by one 
    insertValue( $i );
  }     
  // Closing the connection as best practice  
  mysqli_close($con);


The script above is much slower than the script below:
 
  $con=mysqli_connect("localhost","username","somepassword","anydb");
  if (mysqli_connect_errno())
  {
   echo "Failed to connect to MySQL" ;
   mysqli_connect_error(); 
  }
   
  function insertValues( $val ){
     // Creating query for inserting complete array in single execution.
    $query= " INSERT INTO tableX(someInteger) VALUES .implode(',', $val)";      
    mysqli_query($con, $query);
  }
  
  $data = array();
  for( $i =0; $i<99; $i++){
    // Creating an array of data to be inserted.
    $data[ ]  =   '(" ' . $i. '")' ;
  }
  // Inserting the data in a single call
  insertValues( $data );
  // Closing the connection as a best practice
  mysqli_close($con);

 
12. Frequently Used Switch Cases
Keep most frequently used switch cases on the top.
13. Use Methods in Derived Classes
Methods in derived classes are faster than base classes. For example, let there be a function in both base class and derived class for performing task1. It is named as “forTask1″ in base class and “forTask1again” in derived class, so that they will not override.

Call to the function “forTask1again( )” which is in derived class will work faster than call to the function “forTask1( )” as it is from base class.
 
  class someBaseClass
  {
   public function forTask1($string)
   {
    // perform task 1
   }
   public function forTask2( )
   {
    // perform task 2
   }
  }
  
  class derivedClass extends someBaseClass
  {
   public function forTask1again($string)
   {
    //perform task 1 same as the function in base class.
   }
   public function forTask3($string)
   {
    //perform task 3
   }
  }
  //Instantiating the derived class below.
  $objDerivedClass = new derivedClass( ); 
  
  // The call below works slow for task1 as it is from base class.
  $resultTask1 = $objDerivedClass->forTask1( );
  
  // The call below works faster for task1 as 
  // it is from derived class.
  $sameResultTask1 = $objDerivedClass->forTask1again();
 
14. Use JSON
Use JSON instead of XML while working with web services as there are native php function like json_encode( ) and json_decode( ) which are very fast. If you are bound to have XML form of data, then use regular expression to parse it instead of DOM manipulation.
15. Use isset
Use isset( ) where ever possible instead of using count( ), strlen( ), sizeof( ) to check whether the value returned is greater than 0.
For example, let us assume that you have a function which returns an array with values or a NULL array. Now you want to check whether the returned array is with values or not, then use the following:
if(isset($returnValue)){
  // do something here
}
In this case, use the above code block, instead of the following:
if(count($returnValue) > 0){
  // do something here
}

Thursday, April 3, 2014

PHP - prevent SQL injection in PHP

What is SQL injection ?

 Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted command. It means that SQL queries are able to circumvent access controls, thereby bypassing standard authentication and authorization checks, and sometimes SQL queries even may allow access to host operating system level commands.

 Direct SQL Command Injection is a technique where an attacker creates or alters existing SQL commands to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build an SQL query. SQL Injection flaws are introduced when software developers create dynamic database queries that include user supplied input.

To avoid SQL injection flaws is simple. Developers need to either: a) stop writing dynamic queries; and/or b) prevent user supplied input which contains malicious SQL from affecting the logic of the executed query. This article provides a set of simple techniques for preventing SQL Injection vulnerabilities by avoiding these two problems. These techniques can be used with practically any kind of programming language with any type of database. There are other types of databases, like XML databases, which can have similar problems (e.g., XPath and XQuery injection) and these techniques can be used to protect them as well.



Primary Defenses:

 #1: Use of Prepared Statements (Parameterized Queries)
 #2: Escaping all User Supplied Input

 Here will show simple example of unsafe query :
 $SQL = "SELECT * FROM members WHERE email = '$email' ";
The SQL this time has a WHERE clause added. The WHERE clause is used when you want to limit the results to only records that you need. After the word "WHERE", you type a column name from your database (email, in our case). You then have an equals sign, followed by the value you want to check. The value we want to check is coming from the variable called $email. This is surrounded with single quotes. When an email address is entered in the text box on our form, this value goes straight into the variable without any checks.

When you type that extra single quote on the end, that will be added to the SQL. This is then run on the database. Because it's a stray single quote, you'll get a syntax error. It's this syntax error that an attacker is looking for. Next, the attacker will try to add some SQL to yours. Try this. In the email address textbox, type the following. Type it exactly as it is, with the single quotes:
hi' OR 'x'='x
When you click the Submit button, you should find that there are no errors, and that the username, password and email address are printed out! The attacker is trying to find out whether or not the SQL can be manipulated. If the answer is yes, further attacks will be launched. Can the table and field names be guessed? Can a username and password be guessed? It's this kind of attack that you want to thwart. Try this last one. Enter the following into the email address box:
' OR ''='
Using defense method #1
Php introduce many extensions to communicate with your database . Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

 Using PDO:
$stmt = $pdo->prepare('SELECT * FROM Client WHERE name = :name');
$stmt->execute(array('name' => $name));
foreach ($stmt as $row) {
//row
}

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And gives the developer the chance to catch any error(s) which are thrown as PDOExceptions. What is mandatory however is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Using Mysqli
$stmt = $dbConnection->prepare('SELECT * FROM ClientsWHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // row

}
Using defense method #2
You've got two options - escaping the special characters in your unsafe_variable, or using a parameterized query. Both would protect you from SQL injection. The parameterized query is considered the better practice, but escaping characters in your variable will require fewer changes. We'll do the simpler string escaping one first.

$unsafe_variable = $_POST["user-input"];
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");
Good Luck