What is SQL Injection?

SQL Injection is the process when someone executes one or more SQL statements in your database without your knowledge to harm the data in your database. In this technique, someone exploits a security vulnerability in database application layer. This happens often when you ask for input(s) from the user and they add extra SQL statement(s) with the valid input. We can illustrate this situation with two cases (1) user inputs are incorrectly filtered or (2) user submitted data is not strongly typed and one ore more of the inputs is executed unexpectedly.

This is possible because the user can submit any input and you may have no or weak user submitted input filtering coding, you can execute one more statements in your database at a time.
For example,
Case 1:

Executing one statement at a time is OK.

{code type=PHP}
$sqlStatement = "SELECT * FROM customers where username='james';";
?>
{/code}

Case 2:

Executing more than one statement at a time is also OK.

{code type=PHP}
$sqlStatement = "DROP TABLE users; UPDATE customers SET age=0; DELETE FROM customers where id>0;”;
?>
{/code}

Now an intruder can combine Case 1 and Case 2, run all the queries at a time and then delete the users table and harmfully update the customers table.

As a result, a successful SQL Injection occurs and harms the database information.

Reasons behind SQL Injection

In the following situations, SQL Injection happens.

• Incorrectly filtered escape characters
• Incorrect type handling
• Vulnerabilities inside the database server
• Conditional Errors

Case 1: Incorrectly filtered escape characters

You have a module that asks for user’s email address to send a temporary password to her email address when she forgets her password. So, the usual SQL query will be like this in this case:

{code type=PHP}
$sqlStatement = "SELECT * FROM users WHERE username = '" + $username + "' AND email = '" + $email + "' ";
?>
{/code}

But an intruder can extent this statement if they set the value for the $email variable in this way and delete the user table:

user@hostname.com’; DROP TABLE users; SELECT * FROM customers WHERE name LIKE ‘%

As a result, the final statement is something like:

{code type=PHP}
$sqlStatement = "SELECT * FROM users WHERE username = 'james' AND email = 'user@hostname.com'; DROP TABLE users; SELECT * FROM customers WHERE name LIKE '%'";
?>
{/code}

So, you see the intruder is deleting the users table easily. And as a result, you lose your users table and your system crashes since no user will be able to log in from now on. If you do not have a database backup, you loose everything.

Case 2: Incorrect type handling

Sometimes you definitely know the type of data. For example, the age of a customer is a numeric value, gender of a user as male or female, total amount of bill as double value.

{code type=PHP}
$sqlStatement = "SELECT * FROM customers WHERE age = " + $ageValue + ";";
?>
{/code}

Now what if someone submits the $ageValue value as 20; DROP TABLE users

The resulting sql is:

{code type=PHP}
$sqlStatement = "SELECT * FROM customers WHERE age = 20; DROP TABLE users;";
?>
{/code}

You know for sure that the value of will be always an integer. If you do not check if the value is really an integer, the intruder can add one or more statements with the value of $ageValue variable and harm your database.

Case 3: Vulnerabilities inside the database server

Although some people think that they can avoid SQL Injection just by using mysql_real_escape_string() function in PHP, they are wrong unfortunately. Built-in functions supplied with language package to work on database is sometimes vulnerable to database and thus are not successful to avoid the attack all the times.

Case 4: Conditional Responses

Using SQL Injection the user can easily bypass the sign in or log in process in your system. Let us give an example:

{code type=PHP}
$sqlStatement = "SELECT * FROM users WHERE username = 'james' AND password = 'secret' OR 1=1;";
?>
{/code}

Now you know satisfying only one condition is enough to enter the system. The condition OR 1=1 is always true. Thus, the intruder can fool the log in system.

SQL Injection Prevention and/or Protection

These are the techniques for preventing SQL Injection:

1. Use Parameterized Query
2. Use Stored Procedure whenever applicable
3. Apply Regular expression to discard invalid inputs
4. Write and use Quote blocking  function
5. Hide detailed error messages from the user
6. Create a database user with less privileged role
7. Set the limitation for maximum value in your HTML form

1. Use Parameterized Query

Rather than directly supplying the values in the SQL statement, let us supply the values in parameterized way as follows:

{code type=PHP}
$db_connection = new mysqli("localhost", "user", "pass", "db");
$statement = $db_connection->prepare(“SELECT * FROM customers WHERE id = ?”);
$statement->bind_param(“i”, $id);
$statement->execute();
?>
{/code}

“i” stands for integer type
“d” stands for double type
“s” stands for string type
“b” stands for a blob and will be send in packets

2. Use Stored Procedure whenever applicable

Using stored procedures can help your to reduce the attack risk too.

{code type=PHP}
$sqlStatement = "
CREATE PROCEDURE HUGEORDER
(
id INT ,
quantity INT,
price DECIMAL(6,2)
)
BEGIN
DECLARE discount_percent DECIMAL(6,2);
DECLARE discounted_price DECIMAL(6,2);
SET discount_percent = 10;
SET discounted_price = price – discount_percent/100*price;
IF quantity > 500 THEN
SET discounted_price = discounted_price - 0.25 * quantity;
END IF;
UPDATE fashion_products
SET product_price = discounted_price WHERE product_id = id;
Select * from fashion_products;
END;
";
?>
{/code}

3. Apply Regular expression to discard invalid inputs

Regular expression is very powerful process to find out the validity of the inputs. We can check whether the input is given in proper format. For example, here we validate data for a valid numeric value for customer age and reject any chance for SQL Injection.

{code type=PHP}
if(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email))
{
echo 'INVALID Email Address!';
return;
}
?>
{/code}

You can also user the built in PHP is_array(), is_bool(), is_double(), is_float(), is_int(), is_integer(), is_integer() etc functions to check if the user provided information is in proper format.

4. Write and use Quote blocking  function

If you are using PHP, mysql_real_escape_string function for each and every user given inputs. For example,

{code type=PHP}
$username = mysql_real_escape_string($username, $ dbLink);
?>
{/code}

This is a very powerful built-in PHP function and will stop SQL Injection in most of the cases. I have used it for long time and found it performing great. You can try to inject SQL after you use the mysql_real_escape_string function and test if you can succeed any way. This powerful function rejects the possibility of many clever techniques used by the intruders.

5. Hide detailed error messages from the user

First of all avoid using the built-in MySQL mysql_error() function. The clever intruder can guess many things from the error message and sometimes the error message may show the connection parameters. Using mysql_error() function is good at development stage. But avoid or clean it when you run it in the real server for users or visitors.

The second thing is stop error reporting in PHP. This is simple and one line code.

{code type=PHP}
// Turn off all error reporting
error_reporting(0);
?>
{/code}

The third thing is better you give a customized error message. See an example:

{code type=PHP}
if(!mysql_query($statement))
{
echo 'We are sorry BUT The server is not responding. Please try again later.';
}
?>
{/code}

As a result, the user will not know what the error is and how it is. He will also not get any accidentally disclosed crucial information such as database name, table name, username etc.

6. Create a database user with less privileged role

In most cases, you will notice that the visitors do not need to delete or update any information. Think of a music selling site. The user can request for data (which is SELECT query) and make their orders (which are INSERT query). Even sometimes the SELECT operation is just OK in many sites.

So, create different users with different privileges. For admin grant all the permissions. But for a general visitor, grant only limited permission. For example, I am creating, two connection string for two users.

{code type=PHP}
$visitorDbLink = mysql_connect('host', 'general_user', 'general_user_pass');
$visitorDbLink = mysql_connect('host', 'admin_user', 'admin_pass');
?>
{/code}

And now we can user the $visitorDbLink link for regulating information connecting with the visitors and will use the $visitorDbLink link only for the administrator.

7. Set the limitation for maximum value in your HTML form

We can set that the username cannot be more than 10 chars. Try to use the “maxlength” property for HTML form. An example is like this:

{code type=HTML}

{/code}

As a result, possibility of attack by intruder somewhat goes down since he cannot input enough harmful SQL statement input. But it is not very helpful though and the user can bypass this creating his own form and submitting the custom form.

8. Using a PHP Framework

If you are using a PHP framework like CodeIgniter or CakePHP, the framework will protect you at maximum level from SQL injection. Complain of SQL Injection after using a PHP framework is rare and I did not hear even one still now.

This kind of frameworks are Open Source, very matured and strong nowadays and they do each and everything possible such as from input filtering to URL rewrite. Learning them is easy although it take some time. You can get books and other online materials about this PHP frameworks.

If you are an advanced PHP coder or developer, my suggestion is you better code using a PHP framework you like.

These are all I know about SQL Injection.

Thanks for reading.

Hello!

My subject of this post is about the total number of parameters we send as input from a form or any other way and optimizing the parameter passing. If we have many forms and many input fields in each form, it is often painful to set or order value of GET, POST or other variables one by one.

Passing all POST variables as function parameter in one array

Let us see the following HTML form that has ten fields.

{code type=HTML}

Name

Address

Phone

Fax

Email

Website

Message

{/code}

Now if we want to call a function passing all the input fields, it will look like this:

{code type=PHP}
saveFeedback($_POST['name'], $_POST['address'], $_POST['phone'], $_POST['fax'], $_POST['email'], $_POST['website'], $_POST['message']);
?>
{/code}

But does it look good or anything comfortable? I know most coder will be bored doing this. When I was a newcomer to PHP programming, I used to do this kind of programming. But there is no reason to do coding like this as we have extremely simple solution.

Fortunately we can pass a whole array as function parameter value. This is how:

Function calling:

{code type=PHP}
saveFeedback($_POST);
?>
{/code}

Or the Object Oriented Programming style:

{code type=PHP}
$myObject = new MyClassName();
$myObject->saveFeedback($_POST);
?>
{/code}

Easy and comfortable. Huh?

Getting the values inside the function definition:

{code type=PHP}
function saveFeedback($inputArray)
{
$myName = $inputArray['name'];
$myAddress = $inputArray['address'];
$myPhone = $inputArray['phone'];
$myFax = $inputArray['fax'];
$myEmail = $inputArray['email'];
$myWebsite = $inputArray['website'];
$myMessage = $inputArray['message'];

// Rest of the coding for this function
}
?>
{/code}

Simple. Right?

You can also print all the input values inside your function you want for debugging or any other purpose as like this:

{code type=PHP}
function saveFeedback($inputArray)
{
var_dump($inputArray);
}
?>
{/code}

Passing all GET variables as function parameter in one array

Suppose that here is your link:

http://www.example.com/products.php?type=cap&max_price=1000&min_price=100&material=leather

You can pass all the GET variables to a function such as ‘filterKeys’ in this case simply this way:

{code type=PHP}
filterKeys($_GET);
?>
{/code}

Or the Object Oriented Programming style:

{code type=PHP}
$myObject = new MyClassName();
$myObject->filterKeys($_GET);
?>
{/code}

And here is how you get them:

{code type=PHP}
function filterKeys($inputArray)
{
$productType = $inputArray['type'];
$productMaxPrice = $inputArray['max_price'];
$productMinPrice = $inputArray['min_price'];
$productMaterial = $inputArray['material'];
}
?>
{/code}

This is just like the way used for the POST method.

You can print the input values inside your function anytime you want for debugging or any other purpose like this:

{code type=PHP}
function filterKeys($inputArray)
{
var_dump($inputArray);
}
?>
{/code}

Although REQUEST method will work similarly, I suggest you not using this for avoiding any future confusion and also for the sake of keeping your coding more readable for other developers.

Special Case: File

Actually when you send a file, it contains several data and $_FILES is an array itself. So, the following values come directly to your script without any extra attention:

{code type=PHP}
$_FILES['fileName']['name']
$_FILES['fileName']['type']
$_FILES['fileName']['size']
$_FILES['fileName']['tmp_name']
$_FILES['fileName']['error']
?>
{/code}

So, you can use them directly in your script.

Taking another Special Case in Consideration: Setting default input values

Sometimes you may need to set a default value to one or more parameters. For example:

{code type=PHP}
function saveClientInformation($age=25, gender='Male')
{
}
?>
{/code}

You can define array and scalar inputs altogether as like this:

{code type=PHP}
function saveClientInformation($postInputArray, $getInputArray, $age=25, gender='Male')
{
}
?>
{/code}

Next you can pass array and scalar inputs altogether as like this:

{code type=PHP}
function saveClientInformation($_POST, $_GET, 35, 'Male');
?>
{/code}

So, why should you code like this? Because this will increase efficiency, save some time and make your code clean looking.

Thanks for reading.

Many times in many sites, you have of course seen round or circular corners. Developers often use round corners to give the site more attractive and professional look. When I was new to web development and trying to figure out how to make the tables’ corners round or circular or oval, I thought it must be a very tough task. Because I was searching solution in Google.com and all the solutions I found was either tough or I could not like. Ha ha. Later I found my own way. But I am sure many developers are using this already.

Here I share my easy and simple way:

Round Corner in DIV:

Step One: Creating a colorful table in Adobe Photoshop, Illustrator or any other tool

I have used Adobe Illustrator’s Rounded Rectangle Tool to create a round corner image. Then I saved it as ‘Save for web’, JPG format and in maximum image resolution. Here is the orange color round corner image:

Step Two: Separating the top and bottom

Now I cut 20 pixels from the top of the rounded image and I name it as ’round-top.jpg’. Here it is:

It is 260 pixels in width and 20 pixels in height.

Next I cut 20 pixels from the bottom of the rounded image and I name it as ’round-bottom.jpg’. Here it is:

It is 260 pixels in width and 20 pixels in height.

Step Three: Setting the top

Here is the CSS code:

{code type=CSS}
#topDiv
{
background:url(images/round-top.jpg) left top no-repeat;
width:260px; height:20px;
}
{/code}

And here is the HTML code:

{code type=HTML}

{/code}

Step Four: Setting the mid section

In the mid section we are going to place another DIV and we will put our text, image or any other content inside it. Here is the CSS code:

{code type=CSS}
#middleDiv
{
background:#FF6801; width:240px; height:100%; padding:0px 10px 0px 10px;
}
{/code}

Notice that although the width of other DIVs is 260 pixels, this DIV has only 240 pixels. That means 20 pixels smaller than other DIVs. This is because we are giving 10 pixels padding in left and 10 pixels padding in right. This total 20 pixels padding will be added with our 240 pixels and make the DIV size looking like a 260 pixel DIV.

And here is the HTML code:

{code type=HTML}

{/code}

Step Five: Setting the bottom

Here is the CSS code:

{code type=CSS}
#bottomDiv
{
background:url(images/round-bottom.jpg) left bottom no-repeat;
width:260px; height:20px;
}
{/code}

And here is the HTML code:

{code type=CSS}

{/code}

Step Six: Code altogether

If we put all code together, it will look like this:

Here is the CSS code:

{code type=CSS}

{/code}

And here is the HTML code:

{code type=HTML}

{/code}

And that’s it!

You can see the demo here: Demo of rounded corner in DIV
I suggest you visiting this link, opening the source code and taking a look.

Round Corner in Table:

Step One: Creating a colorful table in Adobe Photoshop, Illustrator or any other tool

Just follow the same as Step One of the Round Corner in DIV section above.

Step Two: Separating the head and bottom

Just follow the same as Step Two of the Round Corner in DIV section above.

Step Three: Setting the table tag

Here is the CSS code:

{code type=CSS}
.roundTable
{
width:260px; border:0px;
}
{/code}

Notice that we have set the table widht to 260 pixels that is equal to our top and bottom images’ width. We do not need to mention the table width to any other place.

And here is the HTML code:

{code type=HTML}

{/code}

Step Four: Setting the top

We simply set the TD background to ’round-top.jpg’ and set its height equals to the ’round-top.jpg’ image which is 20 pixels.

Here is the CSS code:

{code type=CSS}
.topTD
{
background:url(images/round-top.jpg) left top no-repeat;
height:20px;
}
{/code}

And here is the HTML code:

{code type=HTML}

{/code}

Step Five: Setting the middle section for the content

We simply keep 10 pixels padding in both left and right side and set the background.

Here is the CSS code:

{code type=CSS}
.middleTD
{
background:#FF6801; padding:0px 10px 0px 10px;
}
{/code}

And here is the HTML code:

{code type=HTML}

{/code}

Step Six: Setting the bottom

We simply set the TD background to ’round-bottom.jpg’ and set its height equals to the ’round-bottom.jpg’ image which is 20 pixels.

Here is the CSS code:

{code type=CSS}
.bottomTD
{
background:url(images/round-bottom.jpg) left bottom no-repeat;
height:20px;
}
{/code}

And here is the HTML code:

{code type=HTML}

{/code}

Step Seven: Code altogether

Here is the CSS code:

{code type=CSS}

{/code}

And here is the HTML code:

{code type=HTML}

This is a line to show how it is going with the content. This my another line of content.

This is a line to show how it is going with the content. This my another line of content.

{/code}

And that’s it!

You can see the demo here: Demo of rounded corner in TABLE
I suggest you visiting this link, opening the source code and taking a look.

By the way, nowadays I am no more using tables. Rather I have shifted to table-less CSS based DIV style design. And I feel very comfortable using this technique in my DIV structured design.

You can download sample code from here:
Source Code for Round image corner in HTML & CSS using Table or DIV

Thanks for reading.

When I was new to wordpress theme development, I faced a common problem that many others might have faced. That is adding or linking or integrating a static or custom page in your wordpress site. It is very possible that you may prefer to keep one of your page or file linked as the following:

http://www.yoursitename.com/products.php

Sometimes this kind of requirement arrive that you have no other option but to do it.

But you know this kind of link will not directly appear in your wordpress blog or site because of the unique linking structure of wordpress. Most probably you will get a 404 error i.e. file not found error. So, if you want to link directly, you need to follow a simple trick. This trick is simple, small and intereting.

If you want to add and link your custom or static pages, follow these steps and you will get smooth result:

Step One: Changing the header of the php file

In this article, we will add a php file products.php in our wordpress blog. So, please create your products.php file.  Now open you products.php file and add the following two lines at the top of the file and save your file.

{code type=PHP}
define('WP_USE_THEMES', false);
require('./wp-blog-header.php');
?>
{/code}

Remember, these lines will be the topmost lines in your products.php file. OK? See below how I added.

{code type=PHP}
define('WP_USE_THEMES', false);
require('./wp-blog-header.php');
?>

{/code}

Why are we adding these two lines? Because we are telling wordpress that this file will not use wordpress themes and we are going to link it in a different way. So, wordpress do not force to link in its usual way.

Step Two: Saving in the public_html or root folder

Now upload it in the wordpress installation directory so that it looks like:

http://www.yoursitename.com/products.php

Done? Great!

Step Three: Linking the products.php file in a usual way

Now in the code of any wordpress file where you want to link your products.php file, keep the html linking as usual or what we say relative linking. For example, in the contact us page, I am creating such a link in the body section:

{code type=HTML}

Our Products

{/code}

And yes! We are done!
Test now if it works for you.

Thus, you can add any number of pages in your wordperss site. I do not know exactly when you may need to link this way. I had to do it time to time depending on the client’s project requirement.

Thus, you can also protect your pages from permalink (dot)htaccess in wordpress.

Thanks for reading.

Hello!

Recently I am working in a wordpress project where I am converting a total static site to wordpress site. What I do is: (1) from the static pages, I am taking the static text and creating a new wordpress page for each static page. (2) fetching the page content/text by page id and (3) showing them wherever I want.

I have written a function and now I can fetch the content of any page or one more pages just by the page id.

{code type=PHP}

if(!function_exists('getPageContent'))
{
function getPageContent($pageId)
{
if(!is_numeric($pageId))
{
return;
}

global $wpdb;
$sql_query = 'SELECT DISTINCT * FROM ' . $wpdb->posts .
‘ WHERE ‘ . $wpdb->posts . ‘.ID=’ . $pageId;
$posts = $wpdb->get_results($sql_query);

if(!empty($posts))
{
foreach($posts as $post)
{
return nl2br($post->post_content);
}
}
}
}

?>
{/code}

I am using this function to fetch several page data and show them in one page. In the static site, there are several section with different designs. The client want edit each section using wordpress. So, if there are three different sections, I am creating three individual pages for this single page. Next, I am just fetching the content of the three pages by calling my method three times with different parameters and showing the output in one page.

For exampe,

{code type=PHP}

{/code}

Thus, I am just shifting all text and/or content of the static site to wordpress so that the client can edit the site himself. I think this is a simple way or technique if you want to convert your static page to dynamic wordpress site.

You can also customize this one as per your need. For example, you may only need to fetch the page title.

{code type=PHP}

if(!function_exists('getPageTitle'))
{
function getPageTitle($pageId)
{
if(!is_numeric($pageId))
{
return;
}

global $wpdb;
$sql_query = 'SELECT DISTINCT * FROM ' . $wpdb->posts .
‘ WHERE ‘ . $wpdb->posts . ‘.ID=’ . $pageId;
$posts = $wpdb->get_results($sql_query);

if(!empty($posts))
{
foreach($posts as $post)
{
return nl2br($post->post_title);
}
}
}
}

?>
{/code}

You can not only get the page content, but also the post content if you set the value of the “$pageId” equals any post id. You know you can see the page or post id from the admin panel.

By the way,
I have put my functions in the functions.php file so that I can access it from anywhere. I suggest you following the same way i.e. writing all your custom functions in the functions.php file.

Thank you for reading.