Innovation Creates Miracles

MySQL database server it self supports bunch of common SQL commands which can be utilise for data mining. If you wish to build up a web application which uses MySQL database to store data, you will need to familiarize the following 7 sql commands which I categorize it as “Basic SQL Commands”:

1. SELECT
2. UPDATE
3. INSERT
4. DELETE

SELECT Query

Select command is used to read data from the database. Before running SELECT query, you need to know which table name and table fieldname (if you wish to display specific field in the table instead of all). MySQL do support on wild card (*) for field name. Wildcard represents a shortcut for to display all field in mentioned table.

Below are the basic SELECT command:

SELECT <field_name>|* FROM <table_name>

Eg:
SELECT username,password from user_details

Explanation:
This SELECT query is trying to read record from the table named “user_details”. This query will display two field which is “username” and “password”.

Update Query

Update command is used to update existing data in the database. Before running UPDATE query, you need to know which table name and table fieldname to be updated. UPDATE query always comes in pair with the WHERE clause to make sure that the correct record is being updated.

Below are the basic UPDATE command:

UPDATE <tablename> SET <fieldname>=<value> WHERE <fieldname>=<value>

Eg:
UPDATE user_details SET password = “1234556″ WHERE username = “vickson”

Explanation:
This UPDATE query is trying to updates all existing record in the database if the record is having field “username”’s value equal to “vickson”. If the WHERE clause is being stripped off, this will apply to all records in the user_details table which ends with all the record’s password field value is “1234556″.

INSERT INTO Query

This INSERT INTO query is used to insert data into the database. There are two ways to write INSERT query that is :

INSERT INTO <tablename> (<fieldname1>,<fieldname2>,..) VALUES (”data1″,”data2″,…)

OR

INSERT INTO <tablename> VALUES (”data1″,”data2″,…)

Eg:
INSERT INTO user_details (username,password) values (”vickson”,”1234556″);

INSERT INTO user_details values (”vickson”,”1234556″);

Explanation:
This INSERT query is trying to insert data into user_details table with “vickson” as username and “1234556″ as password.

Notice that the second INSERT query do not specify the fieldname, the MySQL will match the values provided according field orders during the table creation.

DELETE Query

By the query name it self, you know this query is to delete a record in the table. Similar to UPDATE query, this DELETE query is recommended to be executed with WHERE clause to make sure you deleted the correct record.

Below are the basic DELETE command:

DELETE FROM <table_name> WHERE <fieldname>=<value>

Eg:
DELETE FROM user_details WHERE username = “vickson”

Explanation:
This DELETE query is trying to delete record in the table which is having username field value as “vickson”. Notice that, if the WHERE clause is being stripped off, this will mean that to delete all records in the user_details table.

By understanding these 4 query commands, there is no problem for you to code simple/basic php application with mysql database. However, you may need to use a more complicated sql queries depends on how complicated is your php application.

�

To have a better security over your website form, especially on the email forms, you may include security code via Images feature where the user is required to enter the security code shown in the images before they can submit the data.

This will be more secure using image than you just provide security code in string format, users with a little knowledge about programming can easily extract the security codes from the html output.

This security code can be easily coded in PHP scripts using GD Library and PHP Session. To have a better understanding, click here to download the sample script. I have split out my sample script into 3 main files:

1. image.php >> Generate random security codes and output the code in image format

2. index.php >> provide a simple form for user to enter the security code by referring to image.php output

3. process_code >> validate the security codes entered by user.

Some of the web hosting do provides domain aliases service such as Exabytes Network. It will be kinda waste if the aliased domain showing the same output as the parent domain.

When having aliased domain sharing the same default page, some work need to be done if you want specific domain to be redirected to other location or path such as :

http://www.oldcoconut.com/ => http://www.yahoo.com/

OR

http://www.oldcoconut.com/ => http://www.oldcoconut.com/any_folder_or_filename

If you are good in php or even with a novice php skill, with some hint from the php manuals, you still can start coding the selective redirection.

Simple selective redirection using php script will involve two component, server variables and header() function. The server variable involved will be SERVER_NAME.

Sample script of selective redirection are as below:

$myHostname = “oldcoconut.com”;
$myHostRedirect = “http://oldcoconut.com/sample“;
if($myHostname==$_SERVER[’SERVER_NAME’])
{
  header(”Location: “.$myHostRedirect);
}

If you do have multiples aliased domain which you wish to have them redirected to other location or folder, you may consider on using arrays to keep the domain and its redirection URL.

If you still want to preserve the domain name on the URI Address, you may consider on using frame to hide the redirected url.

You may refer to the below sample script for further references:

selective_hostname_redirection.zip

Well, I do came accross many programmer who is coding their php script using global variables rather than using predefined variables $_POST or $HTTP_POST_VARS to process the data posted from html forms.

Though using global variables is good on code simplification and better understanding while doing the script, it will leads to security threads over their website script if it is not well scripted.

Exploits such as http injection will be the common security threats on users who deals with global variables form processing.

Others than the security threats, this method is a dependent method where it relies on register_globals settings in php.ini which might be disabled by various hosting providers for security purpose.

You may try at your own by setup two different form processing script. One is using global variables and another is using $_POST or $HTTP_POST_VARS for form processing. Try both script at the same time by turning register_globals off and on to see the results.

Of course if you are using Apache server with PHP, you can still enable the register_global for domain wide by include the following string into the .htaccess file:

php_flag register_globals on/off

May be you may have a try the same script using the same test script (without modifiying the content) on IIS. You will end up scratching your heads on the script modification to suit the IIS environment which is having register_globals turn off or develop/search third party tools to provide feature where you can have the register_globals to be turn on.

If you want your life to be easy, start coding your script using $_POST or $HTTP_POST_VARS rather than global variables. You will tell your client/boss “Yes, no problem!” when they plan to switch webserver with register_globals turned off or even plan to turn off the register_globals on the existing webserver.

Emails is a common feature applied in website application. Even a normal corporate website do include email function for their contact/about us page.

If you are newbie and wish to have this email feature included, well, here are some hint and sample emails function. I will us “Contact Us” form as samples.:

Before getting started, it is a MUST for you to draw a flow diagram on how your email feature works. When drawing out the diagram, make sure that you consider on the security issue that might strike. Many do not aware on this which leads them to security vulnerabilities which will be used by anonymous user to spam on other users.
Email features in contact us page normally will be divided into two part:
HTML forms which posts the user data
PHP Script to process data posted from the HTML forms and send email to the respective mailbox address.
When dealing with the email using PHP scripts, the common built-in function used will be mail() function. This function is a straight forward email function which referring to email engine specified in php.ini settings.
Samples on php mail() function usage will be:
$recipient = “recipient_email@address“;
$sender = “sender_email@address“;
mail($recipient,”Email Subject”,”Email Message”,”From :”.$sender);

ALERT

As I mentioned above, security on the email will be a serious concern as without a strong security measurement applied on the email application, you might end up exposing your script to be used by anonymous user for spam activities which will end up having your domain IP being blacklisted.

Below are the recomended mail script sample:

php_mail.zip

Enabling file upload features on website is quite common now adays especially on forums or image gallery which might be available free, licensed or self developed.

If you do wish to enable file upload feature on your application, please make sure that the feature does not have security holes. I do encounter before some administrator, who believe himself is very good in PHP scripting, uploaded a simple upload file script to ease their work on uploaded their new application script to the website server. When looking at their script…OH MY GOD!!! The script as simple as you can upload what ever file you like without password protection or file type check.

REMEMBER!! There are many anonymous user may be currently scanning your website folder and files. May be you may put the files far inside, as long as your script is available via internet, your website will be vulnerable.

To have a better upload file handling:

1. Password protect the files or grant only privileged user to access to the file upload script.

2. Make sure that you do check on the uploaded file such as the file extension. For eg., if your application which is upload file feature enabled for image uploading, make sure you only allow extension such as jpg, bmp, bmp or other image file extension and flush or throw error when user uploading non-image files.

3. If you wish to allow all user to upload whatever file they want, make sure the uploaded file is outside the website directory so that it cannot be reached via http.

4. If you wish to allow user to upload scripting files, make sure that the folder which kept the files does not have executable permission enable and make sure that the webserver does not map the file extension to any scripting engine or interpreter.

Sample of poor file upload script:

poor_upload_file.zip

Sample of good file upload script:

good_upload_file.zip