Hey, Would you like to work at Home ?? Just click here No need to pay, just register free and activate your account and get data Entry Work at your Home.

Saturday, October 10, 2009

Back Up and Restore a MySQL Database

Back up From the Command Line (using mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

  • [uname] Your database username

  • [pass] The password for your database (note there is no space between -p and the password)

  • [dbname] The name of your database

  • [backupfile.sql] The filename for your database backup

  • [--opt] The mysqldump option

For example, to backup a database named 'Tutorials' with the username 'root' and with no password to a file tut_backup.sql, you should accomplish this command:

$ mysqldump -u root -p Tutorials > tut_backup.sql

This command will backup the 'Tutorials' database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the 'Tutorials' database accomplish the command below. Each table name has to be separated by space.

$ mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql

Sometimes it is necessary to back up more that one database at once. In this case you can use the --database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

$ mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

$ mysqldump -u root -p --all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

--add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

--no-data: Dumps only the database structure, not the contents.

--add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

  • Create an appropriately named database on the target machine

  • Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql

To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Disable the Enter key on HTML form

How to disable the Enter key on HTML form

Normally when you have a form with several text input fields, it is undesirable that the form gets submitted when the user hits ENTER in a field. Some people are pressing the enter key instead of the tab key to get to the next field. They often do that by accident or they are accustomed to terminate field input that way. If a browser regards hitting ENTER in a text input field as a request to submit the form immediately, there is no sure way to prevent that.

Add the below script to the <head> section of your page. The following code disables the enter key so that visitors of your web page can only use the tab key to get to the next field.

<script type="text/javascript">

function stopRKey(evt) {

  var evt = (evt) ? evt : ((event) ? event : null);

  var node = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : null);

  if ((evt.keyCode == 13) && (node.type=="text"))  {return false;}


document.onkeypress = stopRKey;


Wednesday, October 7, 2009

Flash image upload with PHP

Flash Code
System.security.allowDomain("www.tshirtsetc.co.uk");  import flash.net.FileReference;    // The listener object listens for FileReference events.  var listener:Object = new Object();    listener.onSelect = function(selectedFile:FileReference):Void {      upWin._x = 200;    selectedFile.upload("./upload.php");  };    // the file is starting to upload.  listener.onOpen = function(selectedFile:FileReference):Void {    _root.upWin.results_txt.text = String("Uploading " + selectedFile.name + "\n");  };    listener.onHTTPError = function(file:FileReference, httpError:Number):Void {      _root.upWin.results_txt.text = String("HTTPError number: "+httpError +"\nFile: "+ file.name);  }    listener.onIOError = function(file:FileReference):Void {   _root.upWin.results_txt.text = String("IOError: "+ file.name);  }    listener.onSecurityError = function(file:FileReference, errorString:String):Void {      _root.upWin.results_txt.text = String("SecurityError: "+SecurityError+"\nFile: "+ file.name);      }    listener.onProgress = function(file:FileReference, bytesLoaded:Number, bytesTotal:Number):Void {   upWin.loadBar._width = Number(bytesLoaded)/Number(bytesTotal)*300;  }    // the file has uploaded  listener.onComplete = function(selectedFile:FileReference):Void {    upWin.results_txt.text = String("Upload finished.\nNow downloading " + selectedFile.name + " to player\n");        if(position_txt.text == String("Front")){    attachMovie("trans", "transHolder", -16161, {_x:150, _y:120});     downloadImage1(selectedFile.name);    }else{    if(position_txt.text == String("Back")){     attachMovie("trans2", "transHolder2", -16162, {_x:450, _y:120});       downloadImage2(selectedFile.name);    }else{    if(position_txt.text == String("LSleeve")){     attachMovie("trans3", "transHolder3", -16163, {_x:150, _y:120});       downloadImage3(selectedFile.name);    }else{    if(position_txt.text == String("RSleeve")){     attachMovie("trans4", "transHolder4", -16164, {_x:450, _y:120});       downloadImage4(selectedFile.name);    }    }    }    }    Itotal_txt.text = Number(3.00);    _root.upWin._x = 2000;  };    var imageFile:FileReference = new FileReference();  imageFile.addListener(listener);     imageMovie.uploadBtn.onPress = uploadImage;  imageMovie.uploadBtn2.onPress = uploadImage;  imageMovie2.uploadBtn3.onPress = uploadImage;  imageMovie2.uploadBtn4.onPress = uploadImage;    // Call the uploadImage() function, opens a file browser dialog.  function uploadImage(event:Object):Void {    imageFile.browse([{description: "Image Files", extension: "*.jpg;*.gif;*.png"}]);  }    // If the image does not download, the event object's total property  // will equal -1. In that case, display am error message  function imageDownloaded(event:Object):Void {    if(event.total == -1) {      _root.upWin.results_txt.text = String("error");        }  }    // show uploaded image in scrollPane  function downloadImage1(file:Object):Void {   transHolder.umbongo.loadMovie("./uploaded/" + file);  }    // show uploaded image in scrollPane  function downloadImage2(file:Object):Void {   transHolder2.umbongo2.loadMovie("./uploaded/" + file);  }    // show uploaded image in scrollPane  function downloadImage3(file:Object):Void {   transHolder3.umbongo3.loadMovie("./uploaded/" + file);  }    // show uploaded image in scrollPane  function downloadImage4(file:Object):Void {   var randomNum:Number = Math.round(Math.random()*(10000-0))+0;   transHolder4.umbongo4.loadMovie("./uploaded/" + file);  }

Server Side - PHP Code
<?php    move_uploaded_file($_FILES['Filedata']['tmp_name'], './uploaded/'.$_FILES['Filedata']['name']);  copy('./uploaded/'.$_FILES['Filedata']['name'], './timeStamped/'.time().$_FILES['Filedata']['name']);    ?>

For more information goto http://livedocs.adobe.com/flash/9.0/main/wwhelp/wwhimpl/common/html/wwhelp.htm?context=LiveDocs_Parts&file=00001054.html

Tuesday, October 6, 2009

Understanding mod_rewrite Directives Convertion

Converting mod_rewrite directives


The URL Rewriting engine in Abyss Web Server is comparable to the mod_rewrite module in Apache. Both offer similar features but they are not fully equivalent.

This article explains how to convert mod_rewrite directives to URL Rewriting parameters in Abyss Web Server. In most cases, the conversion straightforward. But in some rare cases, and for some of the most obscure features of mod_rewrite, conversion is possible provided that you change or rewrite the rules.

mod_rewrite directives syntax

mod_rewrite directives are put in .htaccess files (in subdirectories) or in the main configuration file httpd.conf.

The following directives are related to mod_rewrite and will be used for conversions:




A typical configuration of mod_rewrite looks as follows:

# Some comments

RewriteBase PATH




RewriteBase is optional. By default, the base path is the current location of the directory where the .htaccess file is. Each time a RewriteRule is found, the base path is updated with the parameter following the directive. The base path is useful when processing RewriteRule parameters.

RewriteCond is also optional. 0 or more RewriteCond directives can precede a given RewriteRule declaration.

RewriteRule is mandatory and ends the declaration of a rule. More sequences of RewriteBase/RewriteCond/RewriteRule could follow to define other rules.

Lines which start with # are comments and should be ignored. Lines referencing other directives are also to be ignored during the conversion as they are not related to mod_rewrite.

Starting the conversion

For each RewriteRule directive, you should create a URL Rewriting rule.

  • Locate the RewriteRule you'll convert.

  • Locate all the RewriteCond directives that precede it directly (there are 0 or more of them).

  • Locate the last RewriteBase directive preceding the RewriteRule you'll convert. You may not find a RewriteBase in some cases.

  • If you've found a RewriteBase directive, the base path of the current rule will be the path that is referenced in that directive. Otherwise, the base path is the virtual path of the directory where the .htaccess file you're converting is located. For example, if there is no RewriteBase, if the .htaccess file you're converting is inside C:\sites\firstsite\forum, and if your Documents Path is C:\sites\firstsite\, then the base path is /forum.

  • Now open Abyss Web Server console, press the Configure associated with the host you'll add the URL rewriting rules to, and select URL Rewriting.

  • Press Add in URL Rewriting rules table to create a new rule.

Conversion of the RewriteRule directive

  • The RewriteRule directive has the following syntax:


    [FLAGS] is optional an may not be always present.

    REGEX is a regular expression. If it starts with ^ but the next character is not /, the regular expression is referencing a relative path. In such a case, you must prepend it with the base path of the rule before using it in Abyss Web Server.

    For example, if the base path is /forum and the RewriteRule directive is:

    RewriteRule ^test/(.*)$ index.php?testId=$1

    then the regular expression we'll use will be:


    If REPLACEMENT does not start with / and is not full URL (starting with http:// or similar), the base path should be prepended too. In the above example, the REPLACEMENT that must be taken into account is:


  • In the Abyss Web Server console, enter in Virtual Path Regular Expression the regular expression used in RewriteRule (after prepending it with the base path if it starts with ^ but the next character is not /).

  • Enter in Redirect to the replacement string used in RewriteRule (after prepending it with the base path if it does not start / and is not full URL).

  • If the RewriteRule directive has flags, convert each one of them as explained below:

    chain/C (chained with next rule)

    From Apache manual: "This flag chains the current rule with the next rule (which itself can be chained with the following rule, and so on). This has the following effect: if a rule matches, then processing continues as usual - the flag has no effect. If the rule does not match, then all following chained rules are skipped." No direct conversion is possible unless you reorder your URL Rewriting rules and correctly set the Next Action in Abyss Web Server for each rule.

    cookie/CO=NAME:VAL:domain[:lifetime[:path]] (set cookie)

    No equivalent in Abyss Web Server.

    env/E=VAR:VAL (set environment variable)

    No equivalent in Abyss Web Server.

    forbidden/F (force URL to be forbidden)

    Set If this rule matches to Report an error to the client and set Status Code to 403 - Forbidden.

    gone/G (force URL to be gone)

    Set If this rule matches to Report an error to the client and set Status Code to 410 - Gone.

    last/L (last rule)

    Set Next Action to Stop matching

    next/N (next round)

    Set Next Action to Stop matching.

    nocase/NC (no case)

    Uncheck Case Sensitive

    noescape/NE (no URI escaping of output)

    Uncheck Escape Redirection Location.

    nosubreq/NS (not for internal sub-requests)

    Uncheck Apply to subrequests too.

    proxy/P (force proxy)

    No equivalent in Abyss Web Server.

    passthrough/PT (pass through to next handler)

    No equivalent in Abyss Web Server.

    qsappend/QSA (query string append)

    Check Append Query String.

    redirect/R[=code] (force redirect)

    Set If this rule matches to Perform an external redirection and set Status Code to the value of code if available or to 302.

    skip/S=num (skip next rule(s))

    From Apache manual: "This flag forces the rewriting engine to skip the next num rules in sequence, if the current rule matches." No direct conversion is possible unless you reorder your URL Rewriting rules and correctly set the Next Action in Abyss Web Server for each rule.

    type/T=MIME-type (force MIME type)

    No equivalent in Abyss Web Server.

Conversion of the RewriteCond directives

Now it's time to convert the RewriteCond directives associated with the RewriteRule we're working on. Remember that only the RewriteCond immediately preceding the RewriteRule are to be taken into account. If there are no RewriteCond directives, conversion is over.

  • A RewriteCond directive has the form:

    RewriteCond %{VARIABLECOND [FLAGS]

    If the first argument of the RewriteCond you're converting contains a string which is not conforming to the syntax %{VARIABLE}, it will be impossible to convert the mod_rewrite rule to an Abyss Web Server URL Rewriting rule.

    [FLAGS] are optional and may not be always present.

    For each RewriteCond, press Add in the Conditions table and enter the value of VARIABLE in the Variable field.

  • If COND is a regular expression preceded by !, select Does not match with in Operator. If it is a regular expression not preceded by !, set Operator to Matches with. Next enter the regular expression in Regular Expression field.

  • Otherwise, COND is one of the following tests:

    <VALUE (lexicographically precedes)

    Set Operator to < and enter VALUE in the field Value.


    Set Operator to >= and enter VALUE in the field Value.

    >VALUE (lexicographically follows)

    Set Operator to > and enter VALUE in the field Value.


    Set Operator to <= and enter VALUE in the field Value.

    =VALUE (lexicographically equal)

    Set Operator to = and enter VALUE in the field Value.


    Set Operator to Is different from and enter VALUE in the field Value.

    -d (is directory)

    Set Operator to Is a directory.


    Set Operator to Is not a directory.

    -f (is regular file)

    Set Operator to Is a file.


    Set Operator to Is not a file.

    -s (is regular file, with size)

    Set Operator to Exists and is not an empty file.


    Set Operator to Does not exist and is an empty file.

    -l/!-l (is/isn't symbolic link)

    No equivalent in Abyss Web Server.

    -F/!-F (is/isn't existing file, via subrequest)

    No equivalent in Abyss Web Server.

    -U/!-U (is/isn't existing URL, via subrequest)

    No equivalent in Abyss Web Server.

  • If FLAGS are present, their conversion should be done as follows:

    nocase/NC (no case)

    Uncheck Case Sensitive.

    ornext/OR (or next condition)

    The only case where a direct conversion is possible is when you have two or more consecutive RewriteCond operating on the same variable. In such a case, the regular expressions of each condition have to be concatenated with a | sign. For example:

    RewriteCond %{HTTP_USER_AGENT} Mozilla [OR]

    RewriteCond %{HTTP_USER_AGENT} Opera [OR]

    RewriteCond %{HTTP_USER_AGENT} Lynx

    could be combined in a single RewriteCond:

    RewriteCond %{HTTP_USER_AGENT} Mozilla|Opera|Lynx

    and thus it suffices to have a single condition on variable HTTP_USER_AGENT which checks if its value matches with Mozilla|Opera|Lynx.

Monday, October 5, 2009

Mod-Rewrite Tricks and Tips - .Htaccess rewrites rules

If you really want to take a look, check out the mod_rewrite.c and mod_rewrite.h files.

Be aware that mod_rewrite (RewriteRule, RewriteBase, and RewriteCond) code is executed for each and every HTTP request that accesses a file in or below the directory where the code resides, so it’s always good to limit the code to certain circumstances if readily identifiable.

For example, to limit the next 5 RewriteRules to only be applied to .html and .php files, you can use the following code, which tests if the url does not end in .html or .php and if it doesn’t, it will skip the next 5 RewriteRules.

RewriteRule !\.(html|php)$ - [S=5]  
RewriteRule ^.*-(vf12|vf13|vf5|vf35|vf1|vf10|vf33|vf8).+$ - [S=1]

.htaccess rewrite examples should begin with:

Options +FollowSymLinks     
RewriteEngine On RewriteBase /

Require the www

Options +FollowSymLinks 
RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} !^www\.yourdomain\.com$ [NC]
RewriteRule ^(.*)$ http://www.yourdomain.com/$1 [R=301,L]

Loop Stopping Code

Sometimes your rewrites cause infinite loops, stop it with one of these rewrite code snippets.

RewriteCond %{REQUEST_URI} ^/(stats/|missing\.html|failed_auth\.html|error/).* [NC]  
RewriteRule .* - [L]  
RewriteCond %{ENV:REDIRECT_STATUS} 200
RewriteRule .* - [L]

Cache-Friendly File Names

This is probably my favorite, and I use it on every site I work on. It allows me to update my javascript and css files in my visitors cache’s simply by naming them differently in the html, on the server they stay the same name. This rewrites all files for /zap/j/anything-anynumber.js to /zap/j/anything.js and /zap/c/anything-anynumber.css to /zap/c/anything.css

RewriteRule ^zap/(j|c)/([a-z]+)-([0-9]+)\.(js|css)$ /zap/$1/$2.$4 [L]  

SEO friendly link for non-flash browsers

When you use flash on your site and you properly supply a link to download flash that shows up for non-flash aware browsers, it is nice to use a shortcut to keep your code clean and your external links to a minimum. This code allows me to link to site.com/getflash/ for non-flash aware browsers.

RewriteRule ^getflash/?$ http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash [NC,L,R=307]  

Removing the Query_String

On many sites, the page will be displayed for both page.html and page.html?anything=anything, which hurts your SEO with duplicate content. An easy way to fix this issue is to redirect external requests containing a query string to the same uri without the query_string.

RewriteCond %{THE_REQUEST} ^GET\ /.*\;.*\ HTTP/  
RewriteCond %{QUERY_STRING} !^$
RewriteRule .* http://www.askapache.com%{REQUEST_URI}? [R=301,L]

Sending requests to a php script

This .htaccess rewrite example invisibly rewrites requests for all Adobe pdf files to be handled by /cgi-bin/pdf-script.php

RewriteRule ^(.+)\.pdf$  /cgi-bin/pdf-script.php?file=$1.pdf [L,NC,QSA]  

Setting the language variable based on Client

For sites using multiviews or with multiple language capabilities, it is nice to be able to send the correct language automatically based on the clients preferred language.

RewriteCond %{HTTP:Accept-Language} ^.*(de|es|fr|it|ja|ru|en).*$ [NC]  
RewriteRule ^(.*)$ - [env=prefer-language:%1]

Deny Access To Everyone Except PHP fopen

This allows access to all files by php fopen, but denies anyone else.

RewriteEngine On  
RewriteBase /
RewriteCond %{THE_REQUEST} ^.+$ [NC]
RewriteRule .* - [F,L]

If you are looking for ways to block or deny specific requests/visitors, then you should definately read Blacklist with mod_rewrite. I give it a 10/10

Deny access to anything in a subfolder except php fopen

This can be very handy if you want to serve media files or special downloads but only through a php proxy script.

RewriteEngine On  
RewriteBase /
RewriteCond %{THE_REQUEST} ^[A-Z]{3,9}\ /([^/]+)/.*\ HTTP [NC]
RewriteRule .* - [F,L]

Require no www

Options +FollowSymLinks  
RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} !^yourdomain\.com$ [NC]
RewriteRule ^(.*)$ http://yourdomain.com/$1 [R=301,L]

Check for a key in QUERY_STRING

Uses a RewriteCond Directive to check QUERY_STRING for passkey, if it doesn’t find it it redirects all requests for anything in the /logged-in/ directory to the /login.php script.

RewriteEngine On  RewriteBase /  
RewriteCond %{QUERY_STRING} !passkey
RewriteRule ^/logged-in/(.*)$ /login.php [L]

Removes the QUERY_STRING from the URL

If the QUERY_STRING has any value at all besides blank than the?at the end of /login.php? tells mod_rewrite to remove the QUERY_STRING from login.php and redirect.

RewriteEngine On  RewriteBase /  
RewriteCond %{QUERY_STRING} .
RewriteRule ^login.php /login.php? [L]

Fix for infinite loops

An error message related to this isRequest exceeded the limit of 10 internal redirects due to probable configuration error. Use 'LimitInternalRecursion' to increase the limit if necessary. Use 'LogLevel debug' to get a backtrace.or you may seeRequest exceeded the limit,probable configuration error,Use 'LogLevel debug' to get a backtrace, orUse 'LimitInternalRecursion' to increase the limit if necessary

RewriteCond %{ENV:REDIRECT_STATUS} 200  
RewriteRule .* - [L]

External Redirect .php files to .html files (SEO friendly)

RewriteRule ^(.*)\.php$ /$1.html [R=301,L]  

Internal Redirect .php files to .html files (SEO friendly)

Redirects all files that end in .html to be served from filename.php so it looks like all your pages are .html but really they are .php

RewriteRule ^(.*)\.html$ $1.php [R=301,L]  

block access to files during certain hours of the day

Options +FollowSymLinks  
RewriteEngine On
RewriteBase /
# If the hour is 16 (4 PM) Then deny all access
RewriteCond %{TIME_HOUR} ^16$
RewriteRule ^.*$ - [F,L]

Rewrite underscores to hyphens for SEO URL

Options +FollowSymLinks  
RewriteEngine On
RewriteBase /  
RewriteRule !\.(html|php)$ - [S=4]
RewriteRule ^([^_]*)_([^_]*)_([^_]*)_([^_]*)_(.*)$ $1-$2-$3-$4-$5 [E=uscor:Yes]
RewriteRule ^([^_]*)_([^_]*)_([^_]*)_(.*)$ $1-$2-$3-$4 [E=uscor:Yes]
RewriteRule ^([^_]*)_([^_]*)_(.*)$ $1-$2-$3 [E=uscor:Yes]
RewriteRule ^([^_]*)_(.*)$ $1-$2 [E=uscor:Yes]  
RewriteCond %{ENV:uscor} ^Yes$
RewriteRule (.*) http://d.com/$1 [R=301,L]

Require the www without hardcoding

Options +FollowSymLinks  
RewriteEngine On RewriteBase /
RewriteCond %{HTTP_HOST} !^www\.[a-z-]+\.[a-z]{2,6} [NC]
RewriteCond %{HTTP_HOST} ([a-z-]+\.[a-z]{2,6})$ [NC]
RewriteRule ^/(.*)$ http://%1/$1 [R=301,L]

Require no subdomain

RewriteEngine On  
RewriteBase /
RewriteCond %{HTTP_HOST} \.([a-z-]+\.[a-z]{2,6})$ [NC]
RewriteRule ^/(.*)$ http://%1/$1 [R=301,L]

Require no subdomain

RewriteEngine On  
RewriteBase /
RewriteCond %{HTTP_HOST} \.([^\.]+\.[^\.0-9]+)$
RewriteRule ^(.*)$ http://%1/$1 [R=301,L]

Redirecting Wordpress Feeds to Feedburner

RewriteEngine On  
RewriteBase /
RewriteCond %{REQUEST_URI} ^/feed\.gif$
RewriteRule .* - [L]  
RewriteCond %{HTTP_USER_AGENT} !^.*(FeedBurner|FeedValidator) [NC]
RewriteRule ^feed/?.*$ http://feeds.feedburner.com/apache/htaccess [L,R=302]  
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]

Only allow GET and PUT Request Methods

RewriteEngine On  
RewriteBase /
RewriteRule .* - [F]

Prevent Files image/file hotlinking and bandwidth stealing

RewriteEngine On  
RewriteBase /
RewriteCond %{HTTP_REFERER} !^$
RewriteCond %{HTTP_REFERER} !^http://(www\.)?askapache.com/.*$ [NC]
RewriteRule \.(gif|jpg|swf|flv|png)$ /feed/ [R=302,L]

Stop browser prefetching

RewriteEngine On  
SetEnvIfNoCase X-Forwarded-For .+ proxy=yes
SetEnvIfNoCase X-moz prefetch no_access=yes  
# block pre-fetch requests with X-moz headers
RewriteCond %{ENV:no_access} yes
RewriteRule .* - [F,L]

27 Apache Request Methods for rewritecond in htaccess


The Request Method, as supplied in the REQUEST_METHOD meta-variable, identifies the processing method to be applied by the script in producing a response.

The script author can choose to implement the methods most appropriate for the particular application.

If the script receives a request with a method it does not support it SHOULD reject it with an error.

List of the 27 Request Methods Recognized by Apache

  1. GET

  2. PUT

  3. POST




  7. TRACE

  8. PATCH



  11. MKCOL

  12. COPY

  13. MOVE

  14. LOCK

  15. UNLOCK





  20. UPDATE

  21. LABEL

  22. REPORT




  26. MERGE



The GET method indicates that the script should produce a document based on the meta-variable values. By convention, the GET method is ’safe’ and ‘idempotent’ and SHOULD NOT have the significance of taking an action other than producing a document.

The meaning of the GET method may be modified and refined by protocol-specific meta-variables.


The POST method is used to request the script perform processing and produce a document based on the data in the request message-body, in addition to meta-variable values. A common use is form submission in HTML [18], intended to initiate processing by the script that has a permanent affect, such a change in a database.

The script MUST check the value of the CONTENT_LENGTH variable before reading the attached message-body, and SHOULD check the CONTENT_TYPE value before processing it.


The HEAD method requests the script to do sufficient processing to return the response header fields, without providing a response message-body. The script MUST NOT provide a response message-body for a HEAD request. If it does, then the server MUST discard the message-body when reading the response from the script.


The OPTIONS method represents a request for information about the communication options available on the request/response chain identified by the Request-URI. This method allows the client to determine the options and/or requirements associated with a resource, or the capabilities of a server, without implying a resource action or initiating a resource retrieval.

Responses to this method are not cacheable.

If the OPTIONS request includes an entity-body (as indicated by the presence of Content-Length or Transfer-Encoding), then the media type MUST be indicated by a Content-Type field. Although this specification does not define any use for such a body, future extensions to HTTP might use the OPTIONS body to make more detailed queries on the server. A server that does not support such an extension MAY discard the request body.

If the Request-URI is an asterisk (“*”), the OPTIONS request is intended to apply to the server in general rather than to a specific resource. Since a server’s communication options typically depend on the resource, the “*” request is only useful as a “ping” or “no-op” type of method; it does nothing beyond allowing the client to test the capabilities of the server. For example, this can be used to test a proxy for HTTP/1.1 compliance (or lack thereof). If the Request-URI is not an asterisk, the OPTIONS request applies only to the options that are available when communicating with that resource.

A 200 response SHOULD include any header fields that indicate optional features implemented by the server and applicable to that resource (e.g., Allow), possibly including extensions not defined by this specification. The response body, if any, SHOULD also include information about the communication options. The format for such a body is not defined by this specification, but might be defined by future extensions to HTTP. Content negotiation MAY be used to select the appropriate response format. If no response body is included, the response MUST include a Content-Length field with a field-value of “0″.

The Max-Forwards request-header field MAY be used to target a specific proxy in the request chain. When a proxy receives an OPTIONS request on an absoluteURI for which request forwarding is permitted, the proxy MUST check for a Max-Forwards field. If the Max-Forwards field-value is zero (“0″), the proxy MUST NOT forward the message; instead, the proxy SHOULD respond with its own communication options. If the Max-Forwards field-value is an integer greater than zero, the proxy MUST decrement the field-value when it forwards the request. If no Max-Forwards field is present in the request, then the forwarded request MUST NOT include a Max-Forwards field.


The PUT method requests that the enclosed entity be stored under the supplied Request-URI. If the Request-URI refers to an already existing resource, the enclosed entity SHOULD be considered as a modified version of the one residing on the origin server. If the Request-URI does not point to an existing resource, and that URI is capable of being defined as a new resource by the requesting user agent, the origin server can create the resource with that URI. If a new resource is created, the origin server MUST inform the user agent via the 201 (Created) response. If an existing resource is modified, either the 200 (OK) or 204 (No Content) response codes SHOULD be sent to indicate successful completion of the request. If the resource could not be created or modified with the Request-URI, an appropriate error response SHOULD be given that reflects the nature of the problem. The recipient of the entity MUST NOT ignore any Content-* (e.g. Content-Range) headers that it does not understand or implement and MUST return a 501 (Not Implemented) response in such cases.

If the request passes through a cache and the Request-URI identifies one or more currently cached entities, those entries SHOULD be treated as stale. Responses to this method are not cacheable.

The fundamental difference between the POST and PUT requests is reflected in the different meaning of the Request-URI. The URI in a POST request identifies the resource that will handle the enclosed entity. That resource might be a data-accepting process, a gateway to some other protocol, or a separate entity that accepts annotations. In contrast, the URI in a PUT request identifies the entity enclosed with the request — the user agent knows what URI is intended and the server MUST NOT attempt to apply the request to some other resource. If the server desires that the request be applied to a different URI, it MUST send a 301 (Moved Permanently) response; the user agent MAY then make its own decision regarding whether or not to redirect the request.

A single resource MAY be identified by many different URIs. For example, an article might have a URI for identifying “the current version” which is separate from the URI identifying each particular version. In this case, a PUT request on a general URI might result in several other URIs being defined by the origin server.

HTTP/1.1 does not define how a PUT method affects the state of an origin server.

PUT requests MUST obey the message transmission requirements.

Unless otherwise specified for a particular entity-header, the entity-headers in the PUT request SHOULD be applied to the resource created or modified by the PUT.


The DELETE method requests that the origin server delete the resource identified by the Request-URI. This method MAY be overridden by human intervention (or other means) on the origin server. The client cannot be guaranteed that the operation has been carried out, even if the status code returned from the origin server indicates that the action has been completed successfully. However, the server SHOULD NOT indicate success unless, at the time the response is given, it intends to delete the resource or move it to an inaccessible location.

A successful response SHOULD be 200 (OK) if the response includes an entity describing the status, 202 (Accepted) if the action has not yet been enacted, or 204 (No Content) if the action has been enacted but the response does not include an entity.

If the request passes through a cache and the Request-URI identifies one or more currently cached entities, those entries SHOULD be treated as stale. Responses to this method are not cacheable.


The TRACE method is used to invoke a remote, application-layer loop- back of the request message. The final recipient of the request SHOULD reflect the message received back to the client as the entity-body of a 200 (OK) response. The final recipient is either the origin server or the first proxy or gateway to receive a Max-Forwards value of zero (0) in the request (see section 14.31). A TRACE request MUST NOT include an entity.

TRACE allows the client to see what is being received at the other end of the request chain and use that data for testing or diagnostic information. The value of the Via header field (section 14.45) is of particular interest, since it acts as a trace of the request chain. Use of the Max-Forwards header field allows the client to limit the length of the request chain, which is useful for testing a chain of proxies forwarding messages in an infinite loop.

If the request is valid, the response SHOULD contain the entire request message in the entity-body, with a Content-Type of “message/http”. Responses to this method MUST NOT be cached.


This specification reserves the method name CONNECT for use with a proxy that can dynamically switch to being a tunnel e.g. SSL tunneling.

Wednesday, September 2, 2009

PHP Fatal error : Out of memory Problem

PHP Fatal error: Out of memoryProblem. Let see how to solve this problem using various techniques.

The first thing I could think of was to restart the Apache httpd service. This immediately solved the issue. but I knew this is not a permanent fix for the issue. When I researched further, I got to know that the
error comes when certain PHP scripts require more memory than PHP was allowed by default.

So the solution is to increase the memory allocated for

PHP. How to do that? There are 4 possible ways -

1. Try looking for the php.ini file. You might find some redundant php.ini files, so make sure you have got the one which is actually being read by PHP. o be sure, create a new php file in your root folder, say “check.php” and have phpInfo(); within the php open and close tags. Execute this file to get the information on where the php.ini is residing. Normally it will be in /usr/local/lib/php.ini

Open the php.ini file in a text editor like TextPad (not in Notepad) and change the values for memory_limit. By default you should see memory_limit = 8M. Try changing it to 12M. If it doesn’t work, increase it to 16M or even 24M and so on.

2. In case you can’t find the php.ini file or do not have access to it, then open up the file which was throwing the error (test.php in my case) and add a line below just after ini_set(’memory_limit’, lsquo;12M’);

3. You can even consider adding a line in .htaccess file which will resolve the issue.
php_value memory_limit 32M

4. Or else, Try adding this line to your wp-config.php file:

Increasing memory allocated to PHP
define('WP_MEMORY_LIMIT', '32M');

If none of the above things solve your issue, then talk to you host.

Note: I am now worrying on which PHP script required an increase in memory allocation. The analysis won’t be so easy though.

Sunday, August 16, 2009

Optimize MySQL response time Techniques

High loaded / Heavy Traffic website can get slow to respond when a lot of different visitors visit sites querying the same mysql database server, making it slow to respond.

There is many ways you can improve mysql server response time:

- by modifying the cache size
- stopping dns resolution ....

Let's see how to implement that.

Sometime it may happen when we got troubles with our databases system. The mysql servers were slow to respond, but when we were logging into those machines, the load was fine, there were quite a few queries going on, but mysql didn't report it was overwhelmed.

1. Disable DNS Hostname Lookup

After seeking out the reason why the traffic wasn't going flawlessly, we determine that the mysql server was doing loads of name resolution queries!!!! What for? Why would that machine to a hostname resolution when only local network machines connect to it.

Seeking out in mysqld manual page, we found that this could be disabled by adding the --skip-name-resolve switch.

Under debian based system, such as ubuntu, knoppix ... and on most linux distribution, mysql configuration files are located in /etc/mysql/my.cnf.

In order to apply the --skip-name-resolve switch when you start mysqld, simply add:


NOTE: When this option is activated, you can only use IP numbers in the MySQL Grant table.

With DNS hostname resolution:

date; mysql -u root -h ; date
Fri Jul 21 23:56:58 CEST 2006
ERROR 1130 (00000): Host '' is not allowed to connect to this MySQL server

Fri Jul 21 23:57:00 CEST 2006

it take 2-3 seconds before the server reply that the client IP is not allowed to connect.

Once DNS hostname lookup is disabled:

date; mysql -u root -h ; date

Fri Jul 21 23:56:37 CEST 2006

ERROR 1130 (00000): Host '' is not allowed to connect to this MySQL server

Fri Jul 21 23:56:37 CEST 2006

The server is replying instantly.

2. Activate Query Cache

After we resolved that issue, we started seeing the database server load increasing, the response time was good after the previous change, but now, we had to lighten a bit the mysql database server's load.

By checking the Query cache memory:

mysql> SHOW STATUS LIKE 'Qcache%';

we could see that no query cache memory was left. It was neccessary to increase the query cache size.

To get an overview of your query_cache variables state, use the following syntax:

mysql> SHOW VARIABLES LIKE '%query_cache%';

You need to have the query cache enabled in the first place (have_query_cache | YES) and make sure that query_cache_type is set to ON. This is usually activated by default on most linux distribution.

Now, you can increase the query cache size (let say you want 50M) using:

mysql> SET GLOBAL query_cache_size = 52428800;

If you want this setting to be kept when restarting mysql, add:

query_cache_size = 52428800;

query_cache_type = 1

3. Summary:

After doing those changes, there were much more queries resolved from the cache, the effect was that the server was responding quickly without calculating too much has most of the queries where cached.

Wednesday, June 10, 2009

Protecting Script using SQL injection For MySQL with PHP

If you are running a dynamic website coded in PHP, chances are you'll be using
MySQL for storing content or information.

MySQL is very well suited for running anything from small personal websites to large corporate
systems, as it is both simple to use and scalable.  However, it is easy to overlook potential
security problems, especially if you don't have much experience.


For instance, you may have a login script for a secure page of your site:


# Database connection code here

$result=mysql_query('select * from users where

.$_POST['username'].'" and pass="'.$_POST['password'].'"');


# Username or password incorrect



# Send user protected page


So for instance, if somebody sent " or ""=" for the username and the password,
the SQL query sent to the database would read: select * from users where user="" or
""="" and pass="" or ""=""
, which would allow access to the protected page without a
valid username or password.  This method is called SQL Injection.


To prevent this from happening, the data provided by the user need to be 'escaped' - this means
putting backslashes in front of quotes, backslashes and other special characters.

This means that the MySQL engine will recognise that the quotes are part of the string, which
prevents SQL injection.

PHP has a built in function that is intended for escaping strings, called addslashes().

For instance, passing the form data from our example through addslashes would result in select * from users where user="\" or \"\"=\""
and pass="\" or \"\"=\""
being passed to the database, which can
be correctly interpreted by MySQL.

Magic Quotes

PHP has a feature called 'Magic Quotes', which automatically escapes get, post and cookie data, as if
addslashes had been called on them.  The idea of this is to prevent scripts written by
inexperienced coders being vulnerable to SQL injection.

However, there are several problems with this feature:

  • Addslashes doesn't escape data exactly right for MySQL databases. (The MySQL function
    MySQL_real_escape_string() should really be used instead)

  • Magic quotes can give programmers a false sense of security, and makes scripts completely
    vulnerable if the option is turned off.

  • It has the irritating side effect that form inputs used in other parts of scripts have slashes added to
    them, which can be very puzzling for beginners, and adds extra coding to remove them again.

Because of these reasons, magic quotes are turned off by default in PHP 5, although they are on
by default in PHP 4.

Best Practice

To keep your code portable and protected against vulnerabilities whether Magic Quotes is enabled or not,
it is best to use a function similar to the one below:


function proper_escape($datastring) {

# Strip slashes if data has already been escaped by magic quotes




# Escape string properly & return

return mysql_real_escape_string($datastring);



Call this when sending input data to the MySQL database like: proper_escape($_POST['username']).

Tuesday, January 20, 2009

MySQL Regular Expressions - Part 3

What are Regular Expressions?

The term regular expressions is one of those technical terms where the words do very little to explain what the technology does. regular expressions are a feature common to many programming languages and are a topic on which entire can, and indeed, have been written. Fortunately (or unfortunately depending on your perspective), MySQL does not provide as extensive support for regualr expressions as some other programming languages. This is good in that it makes the subject area easier to learn, but may be frustrating if you are already proficient with the rich regular expression syntax available in other languages.

Regular expressions are essentially a set of instructions using a predefined syntax for matching text in a variety of flexible ways. For example, you might want to extract all the occurrences of a particular word sequence from a block of text. You might also want to perform a search for a particular piece of text and replace it with some alternate text.

Regular Expression Character Matching

In order to introduce the REGEXP operator, we will begin by looking at a use of regular expressions that could similarly be used with the LIKE operator. As in the previous chapter we need to retrieve rows from a table taking into consideration the difference in spelling of the color gray (grey). To perform this retrieval we will use the regex dot character matching (.). Rather like the LIKE underscore wildcard, this character indicates that any character in this position in the text will be considered a match. For example:

mysql> SELECT * FROM product WHERE prod_name REGEXP 'Gr.y Computer Case';
| prod_code | prod_name | prod_desc |
| 11 | Grey Computer Case | ATX PC CASE |
| 12 | Gray Computer Case | ATX PC CASE (USA) |
2 rows in set (0.05 sec)

Your Ad Here

So far we haven't done anything that could not have been achieved using wildcards. Regular expressions, however, do not stop here.

Matching from a Group of Characters

One problem with the approach outlined above is that any letter between the 'Gr' and the 'y' would have registered as a match. In reality we are only interested in words that contain either an 'a' or an 'e' in that location. Fortunately, regular expressions allow us to specify a group of acceptable character matches for any character position. The syntax for this requires that the characters be places in square brackets at the desired location in the match text. For example:

mysql> SELECT * FROM product WHERE prod_name REGEXP 'Gr[ae]y Computer Case';
| prod_code | prod_name | prod_desc |
| 11 | Grey Computer Case | ATX PC CASE |
| 12 | Gray Computer Case | ATX PC CASE (USA) |
2 rows in set (0.00 sec)

Use of this syntax ensures that only the words 'Grey' and 'Gray will match the search criteria. There is no limit to the number of characters that can be grouped in the brackets when using this filtering technique.

Matching from a Range of Characters

The character group matching syntax can be extended to cover range of characters. For example, instead of declaring a regular expression to cover the letters between A and F as [ABCDEF] we could simply specify a range of characters using the '-' character between the upper and lower ranges [A-F]. We could, for example, list a product based on certian model numbers which begin with numbers ranging from 1 to 6:

mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model [1-6]543';
| prod_code | prod_name | prod_desc |
| 1 | CD-RW Model 4543 | CD Writer |
| 14 | CD-RW Model 5543 | CD Writer |
| 15 | CD-RW Model 6543 | CD Writer |
| 16 | CD-RW Model 2543 | CD Writer |
4 rows in set (0.00 sec)

Handling Special Characters

As you have seen, regular expressions assign special meaning to particular characters. For example the dot (.) and square brackets ([]) all have special meaning. Those who studying critical thinking at college will already be questioning what to do if the character sequence that is the subject of a search contains one or more of these characters. Obviously if you are are looking for the following text that looks like a regular expression, the text for which you want to search is, itself, going to be viewed as regular expression syntax.

To address this issue, a concept known as escaping is used. In SQL, escaping involves preceding any characters that may be mis-interpreted as a regular expression special character with double back slashes (\\). For example, suppose we have a row in our product table which reads as follows:

| prod_code | prod_name | prod_desc |
| 17 | CD-RW Model [7543] | CD Writer |

If we were to search for this without regard to the fact that the prod_name value contains regular expression special characters we will not get what we wanted:

mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model [7543]';
| prod_code | prod_name | prod_desc |
| 1 | CD-RW Model 4543 | CD Writer |
| 14 | CD-RW Model 5543 | CD Writer |
2 rows in set (0.00 sec)

The cause of the problem is that the regular expression has been interpreted as a search for any entries that read 'CD-RW Model' followed by either a 7, 5, 4 or 3. Clearly, what we wanted was the actual text [7543]. If instead we escape the brackets with the \\ escape sequence we get the result we want:

mysql> SELECT * FROM product WHERE prod_name REGEXP 'CD-RW Model \\[7543\\]';
| prod_code | prod_name | prod_desc |
| 17 | CD-RW Model [7543] | CD Writer |
1 row in set (0.00 sec)

Your Ad Here

Regular Expressions and Whitespace Characters

Regular expression syntax also provides a mechanism to reference whitespace characters such as tabs, carriage returns and line feeds. These are referenced in a regular expression using metacharacters. These metacharacters are outlined in the following table:

New line (line feed)
Form feed
Carriage return
Vertical tab

Matching by Character Type

Another useful regular expression trick is to match characters by type or class. For example we might need to specify that a character must be a letter, a number or a alphanumeric. This involves the use of some special class definitions outlines in the following table. Some examples of these special classes in action follow the table:

Class Keyword
Description of Matches
Alphanumeric - any number or letter. Equivalent to [a-z], [A-Z] and [0-9]
Alpha - any letter. Equivalent to [a-z] and [A-Z]
Space or Tab. Equivalent to [\\t] and [ ]
ASCII Control Character
Numeric. Equivalent to [0-9]
Any character with the exception of space
Lower case letters. Equivalent to [a-z]
Any printable character
Characters that are neither control characters, nor alphanumeric (i.e punctuation characters)
Any whitespace character (tab, new line, form feed, space etc)
Upper case letters. Equivalent to [A-Z]
Any hexadecimal digit. Equivalent to [A-F], [a-f] and [0-9]

Let's now look at some examples. Suppose in our product database we have two products with similar names, the 'One&One VoIP Headset' and the "One2One USB Hub'. In order to retrieve the 'One2One' product we would use the [:digit:] character class:

Your Ad Here

mysql> SELECT * FROM product WHERE prod_name REGEXP 'One[[:digit:]]One';
| prod_code | prod_name | prod_desc |
| 19 | One2One USB Hub | 4 Port USB Hub |
1 row in set (0.00 sec)

Similarly, to extract the 'One&One' product we would use the [:punct:] class:

mysql> SELECT * FROM product WHERE prod_name REGEXP 'One[[:punct:]]One';
| prod_code | prod_name | prod_desc |
| 18 | One&One VoIP Headset | Stereo Headset |
1 row in set (0.02 sec)

Regular Expression Repetition Metacharacters

In addition to allowing searches on single instances, regular expressions can also be written to look for repetition in text. this is achieved using a set of repetition metacharacters:

Any number of matches
One or more matches
n number of matches
Not less than n number of matches
A range of matches between n1 and n2
Optional single character match (character my be present or not to qualify for a match)

As always, example do a much better job of demonstrating a concept than data in a table. Let's begin by searching for all 4 digit number sequences in our prod_name column:

mysql> SELECT * FROM product WHERE prod_name REGEXP '[[:digit:]]{3}';
| prod_code | prod_name | prod_desc |
| 1 | CD-RW Model 4543 | CD Writer |
| 2 | EasyTech Mouse 7632 | Cordless Mouse |
| 3 | WildTech 250Gb 1700 | SATA Disk Drive |
| 7 | Dell XPS 400 | Desktop PC |
| 14 | CD-RW Model 5543 | CD Writer |
| 15 | CD-RW Model 6543 | CD Writer |
| 16 | CD-RW Model 2543 | CD Writer |
| 17 | CD-RW Model [7543] | CD Writer |
8 rows in set (0.00 sec)

In the above example we have indicated that we are looking for any sequence of 3 digits by using the digit:{3} regular expression. In this case we have picked up entries with both 3 and 4 digits in a sequence. Suppose, instead we wanted only 4 digit sequences:

mysql> SELECT * FROM product WHERE prod_name REGEXP '[[:digit:]]{4,}';
| prod_code | prod_name | prod_desc |
| 1 | CD-RW Model 4543 | CD Writer |
| 2 | EasyTech Mouse 7632 | Cordless Mouse |
| 3 | WildTech 250Gb 1700 | SATA Disk Drive |
| 14 | CD-RW Model 5543 | CD Writer |
| 15 | CD-RW Model 6543 | CD Writer |
| 16 | CD-RW Model 2543 | CD Writer |
| 17 | CD-RW Model [7543] | CD Writer |
7 rows in set (0.00 sec)

Here we see that our Dell XPS 400 is no longer listed because it has only 3 digits.

The '?' metacharacter is particularly useful when we need to allow for plural words. For example, we may want to list any product descriptions where the word Drive or Drives is used. To achieve this we simply follow the 's' with a '?', thereby making the trailing 's' optional for a match:


Your Ad Here

mysql> SELECT * FROM product WHERE prod_desc REGEXP 'Drives?';
| prod_code | prod_name | prod_desc |
| 3 | WildTech 250Gb 1700 | SATA Disk Drive |
| 20 | MasterSlave Multi-pack | 5 SATA Disk Drives |
2 rows in set (0.00 sec)

Matching by Text Position

The final area of regular expressions to cover in this chapter involves matching based on the location of text in a string. For example, we may want to find a particular match that requires that a word appears at the beginning or end of a piece of text. Once again, this requires the use of some special metacharacters:

Beginning of text
End of text
Start of word
End of word

For example, to search for text that begins with a digit:

mysql> SELECT prod_desc FROM product WHERE prod_desc REGEXP '^[[:digit:]]';
| prod_desc |
| 4 Port USB Hub |
| 5 SATA Disk Drives |
2 rows in set (0.00 sec)

Similarly, to search for text which ends in the word 'Phone':

mysql> SELECT prod_desc FROM product WHERE prod_desc REGEXP 'Phone$';
| prod_desc |
| Smart Phone |
| Mobile Phone |
2 rows in set (0.00 sec)

We can also find instances where string of characters are a separate word. For example if we search for the word 'one' we might get the following:

mysql> SELECT prod_name FROM product WHERE prod_name REGEXP 'one';
| prod_name |
| Apple iPhone 8Gb |
| One2One USB Hub |
| Level One Firewall |
3 rows in set (0.00 sec)

As we can see from the above example, because the word 'Phone' contains the word 'one' we have retrieved more rows than we anticipated. Using the beginning and and end of word metacharacters we can isolate the instances of 'one' which are a standalone word:

mysql> SELECT prod_name FROM product WHERE prod_name REGEXP '[[:<:]]One[[:>:]]';
| prod_name |
| Level One Firewall |
1 row in set (0.00 sec)

MySQL Regular Expressions - Part 2

Regular Expressions in MySQL


A very interesting and useful capability of MySQL is to incorporate Regular Expressions (regex) in SQL queries. The regular expression support in MySQL is extensive. Let's take a look at using Regular Expressions in queries and the supported metacharacters.

Using Regular Expressions in queries

A simple example of using Regular Expressions in a SQL query would be to select all names from a table that start with 'A'.

Code: SQL

SELECT name FROM employees WHERE name REGEXP '^A'

Your Ad Here

A slight modification in the above example to look for names starting with 'A' or 'D' or 'F' will look like this.

Code: SQL

SELECT name FROM employees WHERE name REGEXP '^(A|D|F)'

If we want to select all names ending with 'P', then the SQL query goes like this

Code: SQL

SELECT name FROM employees WHERE name REGEXP 'P$'

We can use much complex patterns in our SQL queries, but first let's have a look at various MySQL Regular Expression metacharacters.

Regular Expression Metacharacters

Matches zero or more instances of the string preceding it

Matches one or more instances of the string preceding it

Matches zero or one instances of the string preceding it

Matches any single character, except a newline

Matches any of x, y, or z (match one of enclosed characters)

Matches any character not enclosed

Matches any uppercase letter

Matches any lowercase letter

Matches any digit

Anchors the match from the beginning

Anchors the match to the end

Separates alternatives

String must occur at least n times, but not more than m times

String must occur exactly n times

String must occur at least n times

Matches beginning of words

Matches ending of words

match a character class i.e.,

[:alpha:] for letters

[:space:] for whitespace

[:punct:] for punctuation

[:upper:] for upper case letters


MySQL interprets a backslash (\) character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\\).

Your Ad Here

Whether the Regular Expression match is case sensitive or otherwise is decided by the collation method of the table. If your collation method name ends with ci then the comparison/match is case-insensitive, else if it end in cs then the match is case sensitive.


Checking only for numbers

Code: SQL

SELECT age FROM employees WHERE age REGEXP '^[0-9]+$'

/* starts, ends and contains numbers */

Contains a specific word, for example the skill PHP in skill sets

Code: SQL

SELECT name FROM employees WHERE skill_sets REGEXP '[[:<:]]php[[:>:]]'

Fetching records where employees have entered their 10-digit mobile number as the contact number.

Code: SQL

SELECT name FROM employees WHERE contact_no REGEXP '^[0-9]{10}$'

For more information refer http://dev.mysql.com/doc/refman/5.1/en/regexp.html

MySQL Regular Expressions - Part 1

A regular expression (regex) is a powerful way of specifying a complex search.

MySQL uses Henry Spencer's implementation of regular
expressions, which is aimed at conformance with POSIX
1003.2. MySQL uses the extended version.

This is a simplistic reference that skips the details. To get more exact
information, see Henry Spencer's regex(7) manual page that is
included in the source distribution. See section C Credits.

A regular expression describes a set of strings. The simplest regexp is
one that has no special characters in it. For example, the regexp
hello matches hello and nothing else.

Non-trivial regular expressions use certain special constructs so that
they can match more than one string. For example, the regexp
hello|word matches either the string hello or the string

As a more complex example, the regexp B[an]*s matches any of the
strings Bananas, Baaaaas, Bs, and any other string
starting with a B, ending with an s, and containing any
number of a or n characters in between.

Your Ad Here

A regular expression may use any of the following special


Match the beginning of a string.

mysql> SELECT "fo\nfo" REGEXP "^fo$"; -> 0
mysql> SELECT "fofo" REGEXP "^fo"; -> 1


Match the end of a string.

mysql> SELECT "fo\no" REGEXP "^fo\no$"; -> 1
mysql> SELECT "fo\no" REGEXP "^fo$"; -> 0


Match any character (including newline).

mysql> SELECT "fofo" REGEXP "^f.*"; -> 1
mysql> SELECT "fo\nfo" REGEXP "^f.*"; -> 1


Match any sequence of zero or more a characters.

mysql> SELECT "Ban" REGEXP "^Ba*n"; -> 1
mysql> SELECT "Baaan" REGEXP "^Ba*n"; -> 1
mysql> SELECT "Bn" REGEXP "^Ba*n"; -> 1


Match any sequence of one or more a characters.

mysql> SELECT "Ban" REGEXP "^Ba+n"; -> 1
mysql> SELECT "Bn" REGEXP "^Ba+n"; -> 0


Match either zero or one a character.

mysql> SELECT "Bn" REGEXP "^Ba?n"; -> 1
mysql> SELECT "Ban" REGEXP "^Ba?n"; -> 1
mysql> SELECT "Baan" REGEXP "^Ba?n"; -> 0


Match either of the sequences de or abc.

mysql> SELECT "pi" REGEXP "pi|apa"; -> 1
mysql> SELECT "axe" REGEXP "pi|apa"; -> 0
mysql> SELECT "apa" REGEXP "pi|apa"; -> 1
mysql> SELECT "apa" REGEXP "^(pi|apa)$"; -> 1
mysql> SELECT "pi" REGEXP "^(pi|apa)$"; -> 1
mysql> SELECT "pix" REGEXP "^(pi|apa)$"; -> 0

Your Ad Here


Match zero or more instances of the sequence abc.

mysql> SELECT "pi" REGEXP "^(pi)*$"; -> 1
mysql> SELECT "pip" REGEXP "^(pi)*$"; -> 0
mysql> SELECT "pipi" REGEXP "^(pi)*$"; -> 1



The is a more general way of writing regexps that match many
occurrences of the previous atom.


Can be written as a{0,}.

Can be written as a{1,}.

Can be written as a{0,1}.

To be more precise, an atom followed by a bound containing one integer
i and no comma matches a sequence of exactly i matches of
the atom. An atom followed by a bound containing one integer i
and a comma matches a sequence of i or more matches of the atom.
An atom followed by a bound containing two integers i and
j matches a sequence of i through j (inclusive)
matches of the atom.

Both arguments must be in the range from 0 to RE_DUP_MAX
(default 255), inclusive. If there are two arguments, the second must be
greater than or equal to the first.


any character which is (or is not, if ^ is used) either a, b,
c, d or X. To include a literal ] character,
it must immediately follow the opening bracket [. To include a
literal - character, it must be written first or last. So
[0-9] matches any decimal digit. Any character that does not have
a defined meaning inside a [] pair has no special meaning and
matches only itself.

Your Ad Here

mysql> SELECT "aXbc" REGEXP "[a-dXYZ]"; -> 1
mysql> SELECT "aXbc" REGEXP "^[a-dXYZ]$"; -> 0
mysql> SELECT "aXbc" REGEXP "^[a-dXYZ]+$"; -> 1
mysql> SELECT "aXbc" REGEXP "^[^a-dXYZ]+$"; -> 0
mysql> SELECT "gheis" REGEXP "^[^a-dXYZ]+$"; -> 1
mysql> SELECT "gheisa" REGEXP "^[^a-dXYZ]+$"; -> 0


The sequence of characters of that collating element. The sequence is a
single element of the bracket expression's list. A bracket expression
containing a multi-character collating element can thus match more than
one character, for example, if the collating sequence includes a ch
collating element, then the regular expression [[.ch.]]*c matches the
first five characters of chchcc.


An equivalence class, standing for the sequences of characters of all
collating elements equivalent to that one, including itself.

For example, if o and (+) are the members of an
equivalence class, then [[=o=]], [[=(+)=]], and
[o(+)] are all synonymous. An equivalence class may not be an
endpoint of a range.


Within a bracket expression, the name of a character class enclosed in
[: and :] stands for the list of all characters belonging
to that class. Standard character class names are:

Name Name Name
alnum digit punct
alpha graph space
blank lower upper
cntrl print xdigit

These stand for the character classes defined in the ctype(3) manual
page. A locale may provide others. A character class may not be used as an
endpoint of a range.

Your Ad Here

mysql> SELECT "justalnums" REGEXP "[[:alnum:]]+"; -> 1
mysql> SELECT "!!" REGEXP "[[:alnum:]]+"; -> 0



These match the null string at the beginning and end of a word
respectively. A word is defined as a sequence of word characters which
is neither preceded nor followed by word characters. A word character is
an alnum character (as defined by ctype(3)) or an underscore

mysql> SELECT "a word a" REGEXP "[[:<:]]word[[:>:]]"; -> 1
mysql> SELECT "a xword a" REGEXP "[[:<:]]word[[:>:]]"; -> 0

mysql> SELECT "weeknights" REGEXP "^(wee|week)(knights|nights)$"; -> 1

Your Ad Here