MySQL Singleton Class with PHP5

The singleton can be use in lot of differents cases. In this exemple we will make a MySQL connection Singleton class. This will help us to always use the same connection.

class MysqlDB{
static private $instance_MysqlDB = null;

private
$objMysqli;

/**
* Instantiate the object
**/

private function __construct(){
$this->objMysqli = new mysqli("localhost", "root", "", "mysql");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
}
/**
* Perform a query
*
* @param string $sql
*/

public function select($sql){
$this->objMysqli->query($sql);
$this->var_dumping();
}
/**
* Var dump the current object
*/

public function var_dumping(){
var_dump($this);
}

/**
* Get the current instance for the object
*
* @return object
*/
static public function getInstance(){
if(self::$instance_MysqlDB == null){
self::$instance_MysqlDB = new self;
}
return self::$instance_MysqlDB;
}
}

---------------------------------------
To use the singleton we will never use the constructor method. We will call the getInstance method.
Here is an example :

include_once("MysqlDb.php");
MysqlDB::getInstance()->select("SELECT * FROM `help_category` LIMIT 5");

Why you shouldn't use Typed Dataset...!

When working with persistent layer in .NET environment, you feel something is needed like hibernate or ibatis as a data access layer which automatically makes relations between tables and object, and give us an opportunity to call stored procedures and queries like we call methods of an object. Although hibernate is ported into .NET version, i don't trust its performance and other issues because of why i don't know. Then i looked around for different solutions and found bingoo Typed Dataset. If you haven't used it or know it, you can have a quick check here; Using Strongly-Typed Data Access in Visual Studio 2005 and ASP.NET 2.0. It has many nice sides such as simplifying many things, lessening code size, fastening development time in persistent layer etc... I have implemented all my tables, stored procedures and views using this method. It was really fun to make them. Click, Next, Select Ding dang finish. And it works fine. But after some time, i begin to see the disadvantages and problems of it. So let me list these problems and warn you not to do the same mistakes i did.

• Database name change
  You created your typed data access layer with wizards. It is okay. But it saves database name, db username information in XSS files, means when you try to deploy your application, you have to use the same database name in a production server. You cannot change the name of the database. Big problem it there is a database with the same name in our production table, or when you try to create two instance of the application which is not possible.
  
• Table structure change
  It is common to change the database tables' structure depending on new requirements or because of the wrong analysis. Maybe we need to change the data type of a column of a table, or length or size of a column. So if you change the table's structure you have also to change your entire typed datasets which are related to that table, which is a big headache. Everytime you change table structure, you also need to change dataset.
• Database username
  I suggest you to create your tables and stored procedures under the name "dbo", not custom user. If you create your stored procedure as a "customuser.SelectProducts" instead of "dbo.SelectProducts", the username "customuser" is stored in XSS files of typed dataset. So you cannot change the user name of the database in any place in the future. Same problem again like "Database name change"

Finally, i want to say that i am writing these problems because i didn't find the solution myself. i will be glad if you share with us, if you know the way how to handle these problem.

Hope it help ;)

See Also;

• Most popular free/open source IDEs and Editors
• Most Useful 20+ Visual Studio Add-ins
• Essential firefox add-ons for web programmers
• Open source .NET libraries you should be already using
• Sharepoint and MOSS 2007 resources, articles and tutorials
• Top 10+ source code search engines
  

SQL Injection Resources

SQL injection is the one of the best known security attack method to web applications. Programmers should know the details of SQL Injection in deep to prevent this kind of attacks. I have collected numbers of resources that may help you to understand the topic well. I hope it helps.If you know other resource, please add as a comment.

Wikipedia definition; SQL injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

• http://www.unixwiz.net/techtips/sql-injection.html
• http://www.youtube.com/watch?v=MJNJjh4jORY
  
• http://ferruh.mavituna.com/makale/sql-injection-cheatsheet/
• http://msdn2.microsoft.com/en-us/library/ms161953.aspx
• http://www.4guysfromrolla.com/webtech/061902-1.shtml
• http://www.securityfocus.com/infocus/1644
• http://www.codeproject.com/aspnet/SqlInjection.asp
• http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp
• http://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java
• http://erlend.oftedal.no/blog/?blogid=20
• http://www.rorsecurity.info/2007/05/19/sql-injection/
• http://www.governmentsecurity.org/articles/SQLInjectionModesofAttackDefenceandWhyItMatters.php
• http://www.acunetix.com/websitesecurity/sql-injection.htm
• http://www.sitepoint.com/article/sql-injection-attacks-safe
• http://www.owasp.org/images/7/74/Advanced_SQL_Injection.ppt
• http://www.0x000000.com/?i=14&bin=1110
• http://www.spidynamics.com/support/whitepapers/WhitepaperSQLInjection.pdf
• http://www.spidynamics.com/support/whitepapers/Blind_SQLInjection.pdf
• http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf
• http://www.net-security.org/dl/articles/IntegrigyIntrotoSQLInjectionAttacks.pdf
• http://www.securiteam.com/securityreviews/5DP0N1P76E.html
• http://calibro.candyham.com/2006/03/08/avoiding-sql-injection/

See Also;

1. ASP.NET AJAX Resources, Samples, Articles, Tutorials and Toolkits
   
2. Frameworks are more important than Programming Languages
3. AJAX Frameworks and Resources
4. Prevent robots from submitting data
5. ASP.NET AJAX Resources, Samples, Articles, Tutoria...