Learning SQL Everyday: Day 1


What is SQL?
SQL is a standard language for accessing databases. SQL lets you access and manipulate databases




SQL is the de facto standard language used to manipulate and retrieve data from these relational databases. SQL enables a programmer or database administrator to do the following:

  • ·      Modify a database's structure
  • ·      Change system security settings
  • ·      Add user permissions on databases or tables
  • ·      Query a database for information
  • ·      Update the contents of a database

RDBMS

RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.
Popular SQL Implementations
This section introduces some of the more popular implementations of SQL, each of which has its own strengths and weaknesses. Where some implementations of SQL have been developed for PC use and easy user interactivity, others have been developed to accommodate very large databases (VLDB).
Microsoft Access
We use Microsoft Access, a PC-based DBMS, to illustrate some of the examples in this text. Access is very easy to use. You can use GUI tools or manually enter your SQL statements.

Oracle
We use Oracle, which represents the larger corporate database world, to demonstrate command-line SQL and database management techniques. (These techniques are important because the days of the standalone machine are drawing to an end, as are the days when knowing one database or one operating system was enough.) In command-line RE l, simple stand+[cedilla] one SQL statements are entered into Oracle's SQL*Plus tool. This tool then returns data to the screen for the user to see, or it performs the appropriate action on the database.
Most examples are directed toward the beginning programmer or first-time user of SQL. We begin with the simplest of SQL statements and advance to the topics of transaction management and stored procedure programming. The Oracle RDBMS is distributed with a full complement of development tools. It includes a C++ and Visual Basic language library (Oracle Objects for OLE) that can link an application to a Personal Oracle database. It also comes with graphical tools for database, user, and object administration, as well as the SQL*Loader utility, which is used to import and export data to and from Oracle.
MySQL
MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.
The MySQL Web site (http://www.mysql.com/) provides the latest information about MySQL software.
And all the examples we will have in each coming posts will be using MySQL DBMS.

Q&A
Q: Why should I be concerned about SQL?
A: Until recently, if you weren't working on a large database system, you probably had only a passing knowledge of SQL. With the advent of client/server development tools (such as Visual Basic, Visual C++, ODBC, Borland's Delphi, and Powersoft's PowerBuilder) and the movement of several large databases (Oracle and Sybase) to the PC platform, most business applications being developed today require a working knowledge of SQL.
Q: Why do I need to know anything about relational database theory to use SQL?

A: SQL was developed to service relational databases. Without a minimal understanding of relational database theory, you will not be able to use SQL effectively except in the most trivial cases.
Q: All the new GUI tools enable me to click a button to write SQL. Why should I spend time learning to write SQL manually?
A: GUI tools have their place, and manually writing SQL has its place. Manually written SQL is generally more efficient than GUI-written SQL. Also, a GUI SQL statement is not as easy to read as a manually written SQL statement. Finally, knowing what is going on behind the scenes when you use GUI tools will help you get the most out of them.
Q: So, if SQL is standardized, should I be able to program with SQL on any databases?
A: No, you will be able to program with SQL only on RDBMS databases that support SQL, such as MS-Access, Oracle, Sybase, and Informix. Although each vendor's implementation will differ slightly from the others, you should be able to use SQL with very few adjustments.




Debugging Made Easy for PHP Developers


For someone, with Java background it is quite irritating to notice that you cant display values to console while using PHP. Debugging in php is usually done using the built in php function error_log. This built in function assumes the following parameters.

error_log(error, type, destination);

The parameters are discussed as follows:

1. error: The error message to log
2. type: Specifies the error log type. The type assumes one of the following values:
    0: error is sent to the server error logging system.
    1: the error is sent via email specified in the destination parameter.
    2: the error is sent through the PHP debugging connections. (only available in PHP3)
    3. the error is sent to the file specified in the destination parameter.

Recently I have been getting many questions about how to log (program output/errors) locally. Hence I have decided to prepare a short code showing how to and add it in this blog.

Basically what I have done is create a small wrapper class that uses the built in php error_log function.
Assume we have the following folder structure:

project-root/lib/PHPDebug.php

N.B. project-root is the folder of your project. For instance, if you are creating a payrolapp then the project-roo will be something like

payrolapp/lib/PHPDebug.php

And if you have a /pages folder where you put your php page file

payrolapp/pages/....

So what we are trying to do here is, we are trying to access the PHPDebug class from one of the pages we have under the /pages folder.

Assume we have just created a ShowAllPayrolDataForEmployees.php page under the pages folder, and we opened that file, the code's content will look something like

<?php
.....
.....
require_once('../lib/PHPDebug.php');//go up one step and look for a file called PHPDebug.php in the //lib folder

$netIncome = $grossPymt - $deductions;
.....
//now to print out the value of $netIncome variable, you could use something like
PHPDebug::printLogText("Net Income : ".$netIncome,"../lib/debug.txt");
//here we are calling the static method printLogText of class PHPDebug. The //printLogText(String,String) takes two parameters.
//parameter 1. Is the error message you want to be printed in the error log file
//parameter 2. Is the location and name of the file
?>

And the content of the PHPDebug.php looks like:


<?php
    class PHPDebug{
        public static function printLogText($text,$fileName){          
            if (file_exists($fileName)){
                error_log($text,3,$fileName);
            }else{
                error_log("Error locating the file [$fileName]",3,$fileName);
            }
        }
    }//end class
?>

Before using the printLogText(string,string) method, make sure you have created the file in the location you want it to be.

In the above example, we assume we have created the file 'debug.txt' under the /lib folder. So when calling the method, we always pass the file where the log is going to be stored.

N.B. You can have multiple files in different folders and by just passing the exact file location and name, you can re-use the same method over and over aging to print out the log values.

This method will make sure if the file exist in the specified location and will open and save some values to it if only it exists. Otherwise, it should not break your code even if the file does not exist.

This is a very simple and local remedy to help you while debugging in php. Hope it will be of some help for some one out there.

Steve Jobs building NeXT

Software Developer's Blog: Wordpress Security

Source blog address:

Software Developer's Blog: Wordpress Security: Here is a post which i have found very important and a must to read. The post was made by one of my dear friend Fasil Girma. I hope you wi...

Wordpress Security

Here is a post which i have found very important and a must to read. The post was made by one of my dear friend Fasil Girma. I hope you will enjoy his advise about wordpress site security.


In this post I am basically going to focus on securing a WordPress website and what to do if you get hacked. But the tips included are also useful for any non WordPress website. So I encourage you to keep on reading even if you don't know anything about WordPress <http://www.wordpress.org/> .

WordPress isn't to blame!

In my very early days of web designing one of my client's website got hacked and I had to explain to my client that it was due to WordPress' weak security. I now feel very ashamed of that incident. But I am not alone when it comes to such generalizations on WordPress' security. Most beginning developers don't go further down the road securing their website after setting it up and uploading it onto the "world wild web". They just expect WordPress to be an angel and magically guard their site against all evil on the web. Yes, they don't even pray. But WordPress isn't to blame.


Below I will show you five simple tips how you can lock your WordPress website against most possible threats on the web.


1.      Look for hosts with experience hosting WordPress website.


This can be your first step towards securing your website. All the coming security measures that we're going to discuss below won't do you good if you don't have a reliable hosting company. It's like ordering the best meal of the poorest restaurant in town. You worry about the food after choosing the restaurant. Some good qualities you should look in a potential hosting company are: solid support, transparency and communication, regular backups and runs the latest technology.

I recommend Dreamhost <http://dreamhost.com/>  for the above reasons and from my own experience.

2.      Update WordPress, themes, and plugins. 

Most hackers use the advantage of security flaws in different themes and plugins so make sure you don't include these in your site before checking how widely they are used and their reputation. Also make sure they are continuously updated by their authors. WordPress gets updated regularly; hence, your website should as well. But always backup before you update anything. In case anything goes wrong you can restore your old website till you figure out what went wrong.

always backup before you update anything!

3.      Grant users the minimum privilege they need to do their job.

Take care when you give your website's users the "Administrator" privilege because that will make them access every part of the website. You might think a well-trained user wouldn't do much damage. That might be true but

what if that user's password has been compromised? So if you decide to give an Administrator privilege make sure you use a different username other than the default "Admin" username. When you use the "Admin"
username, hackers are half way through to break your site. Now they only have to guess your password. Also make sure you use a strong password that includes alphabets (both lower and uppercase), numbers and characters. Use different and strong password for every site login. And you're encouraged to change your password once a month.

4.      Lock down the WordPress admin dashboard (/wp-admin) using an .htaccess.


This will make your admin dashboard invisible to the whole world. You can make you dashboard accessible from a certain IP address, say for example, your office computer. And all other devices trying to access this admin

area won't be able to access it, since they'll have a different IP address. Just create an .htaccess file in your /wp-admin folder and add the following lines to it:

AuthUserFile /dev/null
AuthGroupFile /dev/null
AuthName "Access Control"
AuthType Basic
order deny,allow
deny from all
#IP address to Whitelist
allow from 149.149.149.149

This will make http://your-site.com/wp-admin only accessible from the IP address 149.149.149.149 and other requests to access this page from other IP addresses will not be served by the web server.


5.      Ensure that the permissins on wp-config.php are not world readable especially in a shared hosting environment.



WordPress' wp-config.php file contains key configuration information about your website, like: database connection, authentication, and path to WordPress. And you don't want to tempt hackers by giving them everything they need to have to hack into your website in just one file, wp-config.php. So changing the file permission of this special file, wp-config.php, is not an option. Each server configuration requires

different file permissions but most of the time 600 should work for the wp-config.php. You can use your ftp client to change the file permission of any file on your server.



What to do when you get hacked?



*       Take the site offline. Now. That way you avoid getting a bad rap from search engines and antivirus programs.
*       Let your web host know what happened. 

*       Make a full backup of the infected site. It's helpful for reviewing what happened and in case you mess up something during the repair.

*       Change all of your passwords and the authentication keys in the wp-config.php.
*       Remove any old themes, plugins, and unused code from your server.
*       Update all code on your server. Re-install WordPress so all of the
Word-Press files are overwritten with fresh copies. 
*       Reinstall themes or plugins with fresh copies to make sure no malicious code was inserted.

*       Check that the file permissions on your files are correct, especially wp-config.php and uploads.
*       Remove the rogue code and make sure you check all sites on your hosting account. There are tools that can help scan and clean the infection such as VaultPress <http://vaultpress.com/> . Exploit Scanner
also scans for certain exploits.

*       If you don't have the ability to fix the infected files the best thing to do is restore from a recent clean backup.
*       Check your server access logs. Search for any bad file names that you found on your server, patterns passed as query strings, or dates/times that may clue you in to when the attack happened.


Conclusion


To Secure your WordPress website:

*       Look for hosts with experience hosting WordPress website.
*       Update WordPress, themes, and plugins.
*       Grant users the minimum privilege they need to do their job.
*       Lock down the WordPress admin dashboard (/wp-admin) using an .htaccess.
*       Ensure that the permissins on wp-config.php are not world readable especially in a shared hosting environment.

Thank you fasil for such a wonderful read! Hope for so many more to come soon!

If you would like further information, may be ask the author or read other similar posts by this author, you could visit his blog at http://rasfasil.com/wordpress-website-security/

Software Developer's Blog: What is Multithreading in Java?

Software Developer's Blog: What is Multithreading in Java?: Multithreading is a process of executing multiple threads simultaneously. So at this point we will ask our selves what a thread i...

Software Developer's Blog: What is so Special About HTML5?

Software Developer's Blog: What is so Special About HTML?: HTML5   is a  markup language   for structuring and presenting content for the  World Wide Web   and a core technology of the  Internet ...