Tuesday, November 1, 2011

MySql View DEFINER and SQL Security


Some time back, I tried to backup a database from my server and restored it on my localhost mysql server. It had a few views. On taking backup from server i always getting issue i.e. :

mysqldump: Got error: 1449: The user specified as a definer ('msilink'@'localhost') does not exist when using LOCK TABLES

So I opened the dump file and found that the error was being caused by a line which looks like this :

/*!50013 DEFINER=`msilink`@`localhost` SQL SECURITY DEFINER */

This line appeared in every view definition. So the simple reason was that msilink@localhost, user which had created the view (on the server) and when importing the database on local system there was no user named ‘projects’ in MySql.

For that i just open the file and update the line: "50013 DEFINER=`msilink`@`localhost`" with "50013 DEFINER=`root`@`localhost`"

with the following command(In Linux VI Editor):

:%s/50013 DEFINER=`msilink`@`localhost`/50013 DEFINER=`root`@`localhost`/g

then save and close the file. The above solutions worked for me.

Solution is to create a view with SQL security invoker like this :

VIEW system_users AS SELECT host,user,password FROM mysql.user;

The above statement allows this view to be viewed by any user who invokes the view.
By default the SQL SECURITY is DEFINER which means the definer user can only view it.

:: Alternative Solution ::
Create another user that have all the privileges like as root, viw the following query:

mysql > grant all PRIVILEGES on *.* to msilink@localhost IDENTIFIED BY 'root' WITH GRANT OPTION;

In General:
mysql > grant on to @localhost IDENTIFIED BY '' WITH GRANT OPTION;

That also solve your problem.

Monday, July 18, 2011

CodeIgniter - Routing


Implementation of the routing is the interesting idea to manage short URL re-writing.

Way to implement the routing :
Just open the application/config/routes.php, insert the line at the end of the file.

1) if you want that URL would be http://example.com/work/holiday
here, work is the controller name and holiday is the data that would be dynamically changed. So, to implement this just write like this:
$route['work/(:any)'] = 'work/work_detail/$1';
here (:any) = would accept any type of argument,
work/work_detail : class/function relationship
$1 : argument passed

class Implementation:

class Work extends CI_Controller{

function __construct(){
function work_detail($arg){
echo $arg; //would return holiday

2) If your URL is http://example.com/sevice/data, where service is controller and data would dynamic changed,
but client requirement is URL : http://example.com/data only. then you have to manager class/function relationship implicitly.

open routes.php and insert line at the end of the file.
$route['^(?!work|home).*'] = 'page/info/$1';

here, i used regular expression to bypass control from work,home class, means url : example.com/work and example.com/home not there then it would follow page class.

i.e. if URL : http://example.com/data called then specific routing will transfer control to the page/info :: class/function relationship,

it will help in short URL re-writing cases.
Class Page extends CI_Controller{
function __construct(){

function info($arg){
echo $arg; //would give data

Bye everybody...

CodeIgniter - an open source Web Application Framework

Hi Everybody,

From last 3 month, i was working on codeIgniter Framework. And i found its very interesting tool to manage MVC structure.

I start my work on version 2.0.2, it's the latest version of CI. Here many new feature introduced like :
# Support for PHP 4 is gone, PHP 5.1 is now a requirement.
# CSRF Protection built into the form helper
# Drivers
# Application Packages
# Scaffolding, having been deprecated for a number of versions, has been removed.
# Removed the deprecated Validation Class.
# Plugins have been removed, in favor of Helpers.
# Added routing overrides to the main index.php file, enabling the normal routing to be overridden on a per “index” file basis.
# Added $route[‘404_override’] to allow 404 pages to be handled by controllers.

which are not available in CI older version i.e. 1.7.x versions.

CI has very good User Guide that provide methods of creating web application with best implementation way.
URL of User-Guid: http://codeigniter.com/user_guide/

At the end i like it so much because of its light weight, good user guid and good support on bug fixing.

Bye :)