MySQL Injection Attacks

As a PHP developer, I work quite a bit with MySQL database tables, both in the course of developing new websites / web applications, as well as modifying or adding features to existing websites.
One thing that always surprises me when working on code written by others is how frequently the code is subject to what is called MySQL Injection Attacks.
MySQL Injection Attacks are basically where, due to poorly written code, hackers can execute custom MySQL commands (that the developer did not wish the script to be able to execute). These hacks can range from deleting all data in a database table, to logging in as a user, to even stealing data from databases in certain cases.
Well-written MySQL queries can easily prevent such attacks. However, many developers, either out of ignorance or, well, laziness, just don’t properly write their queries.
Let’s back up a few steps here. Some servers, especially in past years, have had a PHP option called Magic Quotes (deprecated as of PHP 5.3.0) enabled. This option automatically “escapes” special characters in data that users submit, say, through a web form.
This escaping is necessary on all user-submitted data that is used in a MySQL query. However, setting the server to automatically escape all user-submitted data is problematic for several reason.
A much better way of doing it is to escape each piece of user-submitted data right when it is inserted into the query. Most MVC frameworks which contain some database model have their own custom-named function that can do this for you.
For those of you who are writing in raw PHP code, PHP has a built-in mysql_real_escape_string function, which you can use to obtain the same results (many custom database libraries use that function).
Anyway, there is no excuse for programmers to not take this small, basic step in securing their web applications, so make sure you take advantage of the tools at your disposal here.

Having Clause - MySQL

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
The syntax for the HAVING clause is:
SELECT column1, column2, … column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, … column_n
HAVING condition1 … condition_n;
aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Import Unicode CSV Files T MySQL

Unicode UTF-8 comma-separated values (CSV) text files, which are exported or generated by such applications as Microsoft Access or Excel, can be imported to MySQL via LOAD DATA INFILE command. CSV data files that are in a Vietnamese legacy encoding should first be converted to Unicode UTF-8, using UnicodeConverter tool, before proceeding with the import.

Make sure that MySQL default charset is utf8. You may need to create the schema (i.e., database structure and tables) before executing the LOAD DATA command. This can be accomplished manually or by MySQL Migration Toolkit to re-create the schema in MySQL database and then use TRUNCATE command to clear the table (delete all rows) before importing. For example:

mysql> TRUNCATE TABLE authors;

or

mysql> DELETE FROM authors;

The import will be executed as follows:

mysql> LOAD DATA LOCAL INFILE ‘authors.txt’ INTO TABLE authors FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’;

for a CSV file ‘authors.txt’ with records having data fields as follows:

“authid”,”lastname”,”firstname”,”address”,”city”,”country”,”phone”,”email”
1,”Nguyễn”,”Trần”,”Lý Thường Kiệt”,”Sài Gòn”,”Việt Nam”,”848-999-9999″,”nguyentran@yahoo.com”
2,”Lê”,”Lý”,”Phố Quang Trung”,”Hà Nội”,”Việt Nam”,”848-888-8888″,”lely@yahoo.com”

The line terminator ‘\r\n’ is for Windows systems; for Unix/Linux, ‘\n’ is used.

Find N’th Highest Salary.......

SELECT salary
FROM employee a
WHERE 3 = (
SELECT count( salary )
FROM employee b
WHERE a.salary <= b.salary )
LIMIT 0 , 30

Basic Drupal site configuration....

Here I will point out some things that should be done by yourself after you have installed Drupal. It's a basic Drupal site configuration - some things that I always do when I create new Drupal website.

I assume you have installed Drupal on your web server. Installation is very easy, it's a matter of "point and click". Just make sure you create your database before that. More info on Drupal installation can be found in install.txt file that comes with Drupal installation.

So, you've just installed Drupal on your web-server (or your localhost), and now you ask yourself: "Hmmmm..... Now what?". Of course, it depends on the nature of your future website what you want to do with it, but before all that there's some basic Drupal site configuration that I always do, and I suggest you do it as well.

You are presented with a nice blue theme and a welcome message "Welcome to your new Drupal website!" and some additional info on setting up your Drupal website. I suggest you read that text and then do the following:

1. Go on and create your first account by clicking "create the first account." link. That first account that you create will be the admin account for your drupal website! It will have a user id of 1. You can name it "admin" or whatever you like.

2. After that you are back to frontpage of your installation. Go to your admin section, and click on "Modules". On modules listing page, mark checkboxes for the following modules: Contact, Path, Search and Statistics. Click "Save configuration". This will install some additional core modules that are not enabled by default, but I always use them.

3. Enable Clean URLs by visiting admin -> Site configuration -> Clean URLs (you need to have mod_rewrite Apache module enabled for this to work).

4. Go to admin section. If you see a message at the top that says "One or more problems were detected with your Drupal installation. Check the status report for more information.", don't worry about it, we'll fix that in a minute. Click "Site information" and fill in all those fields for your Drupal website. I think they are all pretty much self-explanatory :-)

5. Next, click "File system" in admin section. You will hopefully get a message that "Files" directory was created. Click "Save configuration".

6. Now we need to setup Cron for out Drupal installation. In case you didn't know, cron is a unix process that works in a background all the time and it can execute certain commands for you at any time you tell it to do so. Drupal NEEDS cron job to be set up in order to function properly. So, login to your VPS (or dedicated server) and type following command:

crontab -e

This opens up your cron edit screen. Add the following line to it:

0 9 * * * wget -q http://www.your-domain.com/cron.php

This tells your cron to execute cron.php script at exactly 9am every day. You should be fine with it, although you can choose whatever time of the day you want.

(in case you don't have shell access, and you are on shared hosting, you should have some kind of setting up a cron. I know that in Cpanel you can set up a cronjob very easy, through graphical user interface. If you cannot do that, I suggest you contact you hoisting provider for assistance about setting up your cronjob for Drupal).

7. Ctrl+X and 'Y' and Enter. You should be out of your cron settings program. You can check if you successfully added cronjob by executing
crontab -l

8. Now go back to your browser, to your admin page of Drupal installation. You can run cron manually now, and then you shouldn't do it anymore, since we've just set up a cronjob for Drupal. Now you shouldn’t receive any error messages at the top of the window in admin section.

9. Go to front page of your Drupal website. You are going to create your first piece of content in Drupal.

Create content - > Story

Create it! Be creative ;) Write something just for fun, as we have a little bit more to configure. After you click Submit, your "story will be created. Congratulations, you've just created your first piece of content (node) in Drupal.

10. Go to your webroot directory where your Drupal installation is. Open up your .htaccess file and add following lines to it:

RewriteCond %{HTTP_HOST} ^your-domain.com$ [NC]
RewriteRule ^(.+)/$ http://%{HTTP_HOST}/$1 [R=301,L]

or, if you have 'www' part in your domain name, add this instead:

RewriteCond %{HTTP_HOST} ^(www.)?your-domain\.com$ [NC]
RewriteRule ^(.+)/$ http://%{HTTP_HOST}/$1 [R=301,L]

Save ".htaccess" file.

What this does is that it gets rid of a trailing slash at the end of your Drupal URLs. So, if you type URL with a slash at the end, it will automatically get rid of it. This is extremely important for search engines like Google and your website ranking, that's why we do this.

You should now have a basic Drupal website up and running with minimal number of modules running.

I suggest you now play around your Drupal website, explore the admin section a little bit more, just to see that it's nothing really difficult there. Any questions or suggestions, I'd be happy to read them.