Basic Linux Command in PDF

My new assistant has zero knowledge on Linux so I should prepare him some of basic linux command with some example and description. Even though he can use ‘man’ command to get detail explanation on specific command, he still not to familiarize with the environment and gain some experience.

I have created following PDF where you can view and download it here:

Download (PDF, Unknown)

 

MySQL: Advantages and Disadvantages of Galera

Galera cluster replication are now supported in MySQL InnoDB and Percona XtraDB. Even it is new, this technology is having bright future to be developed and is coming into trend to achieve high availability and scalability on database server.

Advantages:

  • No need to learn new storage engine technology like NDBCluster. Learning new technology will require some time to learn. It just similar to InnoDB with added of cluster functionality.
  • All nodes are equal on read and write at all times. It provides synchronous replication, and can guarantee that replicas are up-to-date and ready for reads or to be promoted to master.
  • It protects you against data loss when a node fails. In fact, because all nodes have all the data, you can lose every node but one and still not lose the data (even if the cluster has a split brain and stops working). This is different from NDB, where the data is partitioned across node groups and some data can be lost if all servers in a node group are lost.
  • Since it is using synchronous replication, replicas cannot fall behind. The write sets are propagated to and certified on every node in the cluster before the transaction commits.
  • Easy to scale with more simple implementation on adding and removing nodes, joining cluster and monitor the cluster status. No need to have management node like MySQL cluster.

Disadvantages:

  • It’s new. There isn’t a huge body of experience with its strengths, weaknesses, and appropriate use cases.
  • The whole cluster performs writes as slowly as the weakest node. Thus, all nodes need similar hardware, and if one node slows down (e.g., because the RAID card does a battery-learn cycle), all of them slow down. If one node has probability P of being slow to accept writes, a three-node cluster has probability 3P of being slow.
  • It isn’t as space-efficient as NDB, because every node has all the data, not just a portion. On the other hand, it is based on Percona XtraDB (which is an enhanced version of InnoDB), so it doesn’t have NDB’s limitations regarding on-disk data.
  • It currently disallows some operational tricks that are possible with asynchronous replication, such as making schema changes offline on a replica and promoting it to be master so you can repeat the changes on other nodes offline. The current alternative is to use a technique such as Percona Toolkit’s online schema change tool. Rolling schema upgrades are nearly ready for release at the time of writing, however.
  • Adding a new node to a cluster requires copying data to it, plus the ability to keep up with ongoing writes, so a big cluster with lots of writes could be hard to grow. This will put a practical limit on the cluster’s data size. We aren’t sure how large this is, but a pessimistic estimate is that it could be as low as 100 GB or so. It could be much larger; time and experience will tell.
  • The replication protocol seems to be somewhat sensitive to network hiccups at the time of writing, and that can cause nodes to stop themselves and drop out of the cluster, so we recommend a high-performance network with good redundancy. If you don’t have a reliable network, you might end up adding nodes back to the cluster too often. This requires a resynchronization of the data. At the time of writing, incremental state transfer to avoid a full copy of the dataset is almost ready to use, so this should not be as much of a problem in the future. It’s also possible to configure Galera to be more tolerant of network timeouts (at the cost of delayed failure detection), and more reliable algorithms are planned for future releases.
  • If you aren’t watching carefully, your cluster could grow too big to restart nodes that fail, just as backups can get too big to restore in a reasonable amount of time if you don’t practice it routinely. We need more practical experience to know how this will work in reality.
  • Because of the cross-node communication required at transaction commit, writes will get slower, and deadlocks and  rollbacks will get more frequent, as you add nodes to the cluster.

Reference: High Performance MySQL 3rd Edition

MySQL User Privileges Explained

If you familiar with MySQL, following are the user privileges which available:

Data

SELECT – Allows reading data
INSERT – Allows inserting and replacing data
UPDATE – Allows changing data
DELETE – Allows deleting data
FILE – Allows importing data from and exporting data into files

Structure

CREATE – Allows creating new databases and tables
ALTER – Allows altering the structure of existing tables
INDEX – Allows creating and dropping indexes
DROP – Allows dropping databases and tables
CREATE TEMPORARY TABLES – Allows creating temporary tables
SHOW VIEW – Allows performing SHOW CREATE VIEW queries
CREATE ROUTINE – Allows creating stored routines
ALTER ROUTINE – Allows altering and dropping stored routines
EXECUTE – Allows executing stored routines
CREATE VIEW – Allows creating new views
EVENT – Allows to set up events for event scheduler
TRIGGER – Allows creating and dropping triggers

Administration

GRANT – Allows adding users and privileges without reloading the privilege tables
SUPER – Allows connecting, even if maximum number of connections has reached, required for most administrative operations like setting global variables or killing threads for other users
PROCESS – Allows viewing processes for all users
RELOAD – Allows reloading the server settings and flushing the server’s cache
SHUTDOWN – Allows shutting down the MySQL server
SHOW DATABASES – Gives access to the complete list of databases
LOCK TABLES – Allows locking tables for the current thread
REFERENCES – No usage
REPLICATION CLIENT – Allows the user to ask where the slaves/masters are
REPLICATION SLAVE – Needed for replication slaves
CREATE USER – Allows creating, dropping and renaming user accounts

10 Simple Mistakes that Webmasters Do

Following point is written from my experience and webmaster observation since becoming server administrator of various web servers:

Directory Browsing Enabled

Depending on your web host server configuration, you might need to check this feature should be DISABLED. If not, it will allow unnecessary access by public user to other files. Plus, others can understand on how your site directories, which is not good.

Bear in mind that directory browsing is being indexed by search engines crawler. This will increase the chance for others to find and simply target your website due to viewable content.

Allow Hotlinking To Static Content

Bandwidth is expensive. Do not allow others to use your content as part of their content and consume your bandwidth. To prevent bandwidth stealing, do not forget to disallow others from hotlinking your static contents including image (.jpg, .png, .gif, .bmp), presentation material (.pdf, .swf, .flv) and script (.js/.css/.xml).

Depending on your web host, there must be embedded function to disable hotlinking to your static content. Contact them for more information.

No Watermark

We must always think that others might steal our images. So do not forget to append watermark to every image in your website.

User will get noticed on the stolen image if they see it in other website. Indirectly, you have been advertised and people will start find the real content, rather than the duplicated one. More traffic will coming in.

PHPinfo Page is Accessible

During the web development process, PHPinfo is one of the things that developer need to have in order to understand the web server environment. Even though the PHPinfo page is not retrievable via search engine, this file MUST not exist in your web server or not accessible publicly if your site has go live.

Most webmaster forget to delete this page after development process completed, which means you are exposing the web server environment to the world.

Ignore Website Appearance in Linux and Mobile Device

Most webmasters will try to test run their website in all browsers run on Windows or Mac. Assuming that Linux and mobile device are using the same browser engine, they usually forget that the appearance might be different in other boxes. Even though at this moment Linux and mobile users are less than 7% of total operating system market shares (statistic by W3schools), you should not ignore them entirely.

The site’s font might look standard in Windows, but in Ubuntu it will look slightly bigger due to system font default size is different. Same goes to mobile device which font looks smaller.

Open Hyperlink in Same Windows

Make sure your hyperlink inside your content will be opened in new tab/windows with <target=”_blank”> HTML tag.

Do not interrupt your user experience while they are accessing the content. Many webmaster forgot about this resulting bad experience for users because they have been redirected from the information that they actually want.

Display Email Address on the Website

Email addresses are easily harvested by address-harversting bots. They just need a search engine to build the list of victims site and read the html tag: “mailto:” or seek for complete email address format which is “[email protected]“. You might start getting spam mails usually from 3 to 6 months after the email address publicly displayed, unless your site block search engine crawler to access.

There are some alternative way to display your email address on the website like using CloudFlare service, where they protect your website email address or follow this example at http://csarven.ca/hiding-email-addresses on how to hide email address in HTML.

The best solution is never reveal your email address and use contact form instead.

No CAPTCHA for Form

Do not ever expect your website visitors are all humans. There are many bad bots (comment spam bots, forum spam bots) out there try to do nasty things with your website, which mostly to generate backlinks for Search Engine Optimization (SEO).

Use CAPTCHA (Completely Automated Public Turing Test To Tell Computers and Humans Apart) for every forms you have like comment form, contact form or registration form. Even some CAPTCHA are breakable as refer to here. The most popular CAPTCHA provider nowadays is RECAPTCHA, which acquired by Google. It is a free service to use while preventing spambots from messing up your site.

Backup in the Same Server

Your website backup should NOT exist in the same server of your web server. Especially if the backup directory can be accessible publicly. Usually, webmaster will create backup from inside the web server. It should then download and remove the backup file from the web server. Some webmasters forget to remove the backup files, which then filling up the disk space and unintentionally turn the backup files downloadable by others.

The best backup practice is to have a remote backup repository server and scheduled to be run on daily basis during non-peak hours.

Simple Password Usage

Do not use simple password for any credentials in your website or web host service. Hackers and bots can simply gain access from any point of authentication like email account, database user, protected directory user, back-end system user, web hosting service user and FTP user if they succeed on guessing your password, usually by using brute-force method.

The best password practice is to have alphabet, numerical and symbols combined in your password which more than 10 characters and always change your password within 3 months, at least.

Conclusion

Simple mistake can lead to bigger problem if we are not careful and realize the consequences that we might face. Standard of procedure, checklist and reminder are some methods to overcome humans’ common mistake which is forgetful.

The Best Way to do Server Documentation

As a server administrator, it is very important to have a knowledge base section as our reference point. Forget can never be forgiven. You can use any online documentation tools like Google Docs, Zoho, Evernote and  Office 365. For me I will use Microsoft Office OneNote 2010, which is come in my office laptop as part of Microsoft Office Professional Plus 2010.

The key features that I am looking is transparent synchronization with my online docs. Since I have my Windows Live login, which previously registered for MSN Messenger, I can integrate my local OneNote in my laptop into OneNote in Windows SkyDrive. How cool is that?

The best thing about OneNote is you can write, copy/paste, erase, draw just like a note book. It also has protected section where you can put and store your sensitive and confidential information securely.

Following YouTube video is simple tutorial on how to use OneNote:

Requirement:

OS: Windows 7 Home Premium 64bit
Office application: Microsoft Office OneNote 2010
Windows Live ID: [email protected]
Web browser: Internet Explorer 9 64bit

1. First of all, register your Windows Live ID (if you dont have) at https://signup.live.com/ so we can sync our notes to SkyDrive.

2. Login into SkyDrive at https://skydrive.live.com using Internet Explorer. Click the OneNote icon as screenshot below and create new Notebooks:

 

3. You should see something like screenshot below. I will then create my own section called “Linux” and another new page called “My 1st Documentation“:

4. Now open our Microsoft OneNote in the laptop. Go to Open > Open from the Web > Sign In. Enter you Windows Live ID credentials and click OK.

5. OneNote will then try to retrieve the online Notebooks associated with the user. Once done, click to the Notebooks list “ServerDocumentation” and then it will try to load the content of this notebook on the local OneNote. Below screenshot is what it looks alike when loaded into our local OneNote:

Done! You now have your tools to save all your notes, knowledge base, clips, and much more which accessible all around the world. You can choose to use local OneNote in your laptop or use OneNote WebApp which available at SkyDrive by accessing it using your web browser (not specifically IE). This application do not have “Save” button because it is automatically saved on any changes happen on both sides. Just like your notes!

Eliminate Web Site Bad Traffic

Nowadays, web hosting has introduced many kind of internet traffic, which we can classified into 3 categories:

  1. Clean/good traffic
  2. Bot/crawler traffic
  3. Threat/bad traffic

Threat or bad traffic can harm the website and also can bring serious effect to the server, if you not have security in mind. Following example explained how bad traffic can bring consequences to your website:

  • You receive so many spam comments in your blog post with different IPs
  • Your website is being targeted by DOS or DDOS attack
  • Your website being injected with malicious code. This will usually happened if you have Javascript embedded in your HTML code.
  • Your website being tagged as ‘The site may harm your computer’ by Google Safe Browsing
  • You being accused by the web browser to be hosting malware

What we really want is to accept only clean traffic to our website. The most easiest way to achieve this objective is to use Cloudflare service. Cloudflare will convert your ‘naked’ and ‘exposed’ website into a protected website. The concept is they will route every single web traffic into their cloud network to filter out bad and good traffic, then just forward the good traffic to your website. This service is FREE for life!

What you need to do is:

  1. Go to cloudflare.com and register
  2. Follow the installation wizard online
  3. Change your domain name server to their name server at the domain registrar
  4. Wait for the propagation complete
  5. Done. You are protected!

Since the connections is routed to their network (because we will using their name server), they can log almost full information of our website traffic, not like Google Analytics or Quantcast, where they do tracking using Javascript which embedded into your website. Their reporting is also informative and we can see daily report on what is going on to our web traffic. Example as below:

 

From the screenshot above, you can see that I have report on how many good, bot and bad traffic to my website, how many bandwidth has been saved, how many processed request can be saved (by eliminating bad request) and so on.

I am not doing this for their behalf as promotion or what. It is worth to try. I just want to share with you on how to achieve best result with simplest and most effective way!

System Administration: Managing Remote Location

As a system administrator which administrating many branches, I need to support the end-user environment as well. Doing this from single location is quite hard and I need to create the best environment to manage all of these stuffs efficiently.

I am listing out some tips or what we can do to improve communication and collaboration between branches:

VPN between branches

  • VPN has ability to bring all of the computers in different branches connected to each other via a secured network. This will make sure that data communication between colleagues is protected and user can feel like they are in one single place.
  • The recommended way to do this is to setup a VPN (PPTP) server at one location (lets say head quarter). Create VPN account and assign to everyone in the company with dedicated internal IP (for better tracking).
  • All sensitive information should be located in one place and can only been accessed via VPN connectivity. This will prevenet data leakage and you have logs to every access to the internal system via VPN server.

Internal instant messaging system (chat)

  • Instant messaging is important to improve communication and collaboration. You can use any messaging service available online like GTalk, MSN Messenger, Yahoo Messenger and Skype. It depends on you, but it is highly recommended to use internal instant messenger system like Microsoft Lync 2010, BigAnt Office Messenger, Outlook Messenger and many more.
  • Using internal messenger will give some advantages like:
    • Prevent employee to chat with gossip friends (if you are using public messenger like GTalk and MSN)
    • Simple file transfer and sharing
    • Can back trace the chat history, if the boss suspected something is not right with employee (Good for the boss!)
    • Prevent outsider from sniffing your conversation

DMZ zone

  • Depending on how your network infrastructure being setup, you may need to have DMZ zone available to secure the internal network. DMZ zone is something that we called ‘another network zone that exposed to the public network’. Basically, it help you to isolate your internal network and in the same time able can connect to the web server that exposed to the public network.
  • Example of simple DMZ setup:
  • This is example if you not setup a DMZ with same peripherals as above:
     You can notice how unsecured it can be if you include the servers in one internal network.
  • To setup DMZ, what you need to do is just:
    • Create another network in your router with another subnet and IP range
    • Make sure the incoming connection from public network to web and email service to DMZ only via router
    • Make sure your internal LAN can be connected to DMZ via router
    • Make sure your external firewall blocks all incoming connection unless for web, email and NAT

Network drive and file sharing

  • File sharing and network drive is needed whenever users need to send big files, usually more than 10MB which usually not recommended to be sent via instant messenger or email.
  • The most popular and easy to setup file sharing is SAMBA, where you can map directly in each PC to the public sharing directory. SAMBA server/client comes by default for Windows, Mac and Linux.
  • Using VPN which connect all employees in one secure network will make SAMBA easier to setup and implement.
  • Other file sharing protocol like FTP might be time consuming to setup and slow due to binary data transfer. NFS in other hand is not come by default in Windows and you need to install the client to connect.

Collaboration portal

  • This is really important if you rely on the teamwork. Collaboration portal is something that most of companies think that is is a waste and should not be implement. This is wrong. I am suggesting you to try any collaboration portal and install it in your private server. Play around with that and you will see the importance of it.
  • Collaboration portal can help you to achieve:
    • Create, manage and monitor project, task and  report and assign it to users
    • One central point to store and share confidential document
    • Applying leave and check leave balance
    • Synchronize and connect the account to mail, calendar, instant messaging, active directory, CRM and other services
    • Edit the any document online, without need to download and resend it back
  • There are a lot of collaborative software available in the market and some of it is open-source. You can browse the list at http://en.wikipedia.org/wiki/List_of_collaborative_software

This is what I manage to setup the network office on company that I am working for. Do share with us if you have more point to highlight!

The Philosophy of System Administration

I found this articles in Red Hat Enterprise Linux Introduction to System Administration hand book. I highlight it here for knowledge sharing.

Although the specifics of being a system administrator may change from platform to platform, there are underlying themes that do not. These themes make up the philosophy of system administration.

 The themes are:

  • Automate everything
  • Document everything
  • Communicate as much as possible
  • Know your resources
  • Know your users
  • Know your business
  • Security cannot be an afterthought
  • Plan ahead
  • Expect the unexpected

Automate Everything

Most system administrators are outnumbered either by their users, their systems, or both. In many cases, automation is the only way to keep up. In general, anything done more than once should be examined as a possible candidate for automation.

Continue reading “The Philosophy of System Administration” »

Spam, Spammer, Spambots = Money

Spams, spammers and spambots are exist for only one purpose, money.

1. Spam is email that is sent to other people without being requested. Why they want to disturb our life? Because this is one way of advertising.

2. Spammer will mostly send you something that you don’t know and don’t want to know, and turn to make you know, which equal to advertising. Advertising agency usually get paid for publishing advertisement, newsletter, social announcement and many more, so do spammer. Spammer get money for doing advertising on bad things like replica stuffs, pills, porn, multi-level marketing etc. In short word, spammer is ‘bad advertising agency’.

3. Spammers are not stupid. They have capabilities to be hackers, software developers, system engineers, researchers who tend to get more money which will bring themselves happiness, with less effort. They know how to do things right, do automation for their spamming task, bypass all security features and build many ‘add-on features’ in order to bring the ‘advertisement’ directly to you.

Continue reading “Spam, Spammer, Spambots = Money” »

PHP Handler: DSO vs CGI vs SuPHP vs FastCGI

What is PHP Handler?

PHP handler supplies the required library to interpret PHP code. Each handler delivers the libraries through different files and implementations. Each file and implementation affects Apache’s performance, because it determines how Apache serves PHP.

You need to make the right decision on how Apache should handle your websites or web applications. I am using WHM/cPanel to manage Apache and PHP, I will focus more on advantages and disadvantages from cPanel point of view rather than how to install, implement or switch between each handler.

DSO

PHP runtime is loaded once, when Apache starts up and then reused for all requests.

Architecture:

Advantages:

  • Since DSO is only loaded once, it is faster than CGI and SuPHP
  • PHP can direct access to some Apache-specific calls, which gives you some more fine-grained control on the HTTP-level.
  • Allow for most common PHP .htaccess (php_flag) directives to be used.
  • Good for single sites that require performance over ease of use and security
  • Suitable environment to run PHP optcode caching addon such as eAcclerator, APC or Xcache
  • Well suited for low and high traffic sites but not for CMS systems like Joomla

Disadvantages:

  • PHP processes are handled by the user that is running httpd. In most cases, this user is the ‘nobody’ user. This means when PHP interacts with files on the file system, they have to be accessible by the ‘nobody’ user. This creates permissions issues as your normal cPanel based user will not have access to read/write files that are owned by the ‘nobody’ user without the correct permissions changes. Most PHP web scripts need to write to files and directories and if they are owned by the cPanel user, without changing the permissions on the files or directories to 777, it will cause issues and in some cases, break your website
  • Runs not under user who owner of the site, so you will have to manually manage the permissions on a per user basis to ensure that your PHP apps/scripts can read and write to the files and directories of which it needs to function
  • Any changes on PHP configuration, will required to restart Apache service to make sure the module being reloaded

CGI

A new PHP CGI process is invoked on each Apache request for PHP processing.

Architecture:

Advantages:

How to Increase Email Reliability

Have you encountered valid email being delivered to your Junk/Bulk/Spam? Why is this happened since you are not a spammer? How to make sure my email going through to the Inbox?

We called this as false-positive. False positives are innocent emails that get mistakenly identified as spams. Recent mail system security has incredibly tighten due to number of spam pattern increase. Before your email being delivered to respective mailbox, the email being filtered based on recipient mail server rules.

Example of filtering that can happened in recipient mail server:

1. PTR checking (pointer record or Reverse DNS)
2. SPF checking (Sender Policy Framework)
3. Bayesian Filtering
4. SpamAssassin Server Scoring and Filtering
5. RBL (Real-time Blackhole List)

What we can do?

Depending on how tight is the filtering level, false-positive can happen in any mail server. This is quite annoying since you cannot do anything from your side to fix this. What you can do from your side then? You can use following tips to bring up email’s reliability:

  • PTR – You must use a SMTP server to relay your email to the recipient. That server must have a public IP which recipient can see. That public IP must have a reverse lookup value. Example:
    • Public IP: 154.80.143.22. Hostname: mail.myserver.net
    • When you lookup mail.myserver.net, you surely can get 154.80.143.22, but when you reverse lookup 154.80.143.22, do you get the same result (mail.myserver.net)?
    • How to create PTR records? You MUST contact the IP owner, which can be found from whois page.
  • SPF – This one is useful to tell the world that your domain’s email address should come from certain IP address. Every spammer can use your domain as “FROM:” field, SPF checking will make sure the domain send from, match the sender IP specified in SPF record. You can generate the SPF records from OpenSPF and apply into DNS records of your domain (TXT records).
  • DKIM – This is quite new technology, where sender prove the email comes from them by signing the email with digital signature. You can browse around to see how to enable DKIM for your domain/server.
  • dnsbl.info – Make sure your SMTP IP address is not listed in this website, http://www.dnsbl.info/ . This website can tell whether your IP is in any RBL list or not. If listed, contact the anti-spam organization that list your IP and request for removal. You might need to follow their requirement for that.
  • Click “Not Spam” – Usually, if the sender is using a new domain and do not have any transaction with that particular mail server previously, it will mark your email as spam especially for email service provider like Yahoo, Gmail and Hotmail. If it happens, make sure you click “Not Spam” or “Not Junk” to let the the mail server know that this is a valid email and should be sent to inbox.

If you have done everything as list above but still cannot pass through the inbox, something is not right on the recipient side. Contact their system administrator and let them know about this so they can whitelist you inside their server.

Leave a comment if you have more point to share. Cheers!


MySQL General Security Guidelines

1. Do not ever give anyone (except MySQL root accounts) access to the user table in the mysql database! This is critical!

2. Learn the MySQL access privilege system. The GRANT and REVOKE statements are used for controlling access to MySQL. Do not grant more privileges than necessary. Never grant privileges to all hosts.
Checklist:

  • Try mysql -u root. If you are able to connect successfully to the server without being asked for a password, anyone can connect to your MySQL server as the MySQL root user with full privileges! Review the MySQL installation instructions, paying particular attention to the information about setting a root password.
  • Use the SHOW GRANTS statement to check which accounts have access to what. Then use the REVOKE statement to remove those privileges that are not necessary.
  • Do not store any plain-text passwords in your database. If your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead, use MD5(), SHA1(), or some other one-way hashing function and store the hash value.
  • Do not choose passwords from dictionaries. Special programs exist to break passwords. Even passwords like “xfish98” are very bad. Much better is “duag98” which contains the same word “fish” but typed one key to the left on a standard QWERTY keyboard.
  • Another method is to use a password that is taken from the first characters of each word in a sentence (for example, “Mary had a little lamb” results in a password of “Mhall”). The password is easy to remember and type, but difficult to guess for someone who does not know the sentence.

3. Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).
Checklist:

  1. Try to scan your ports from the Internet using a tool such as nmap. MySQL uses port 3306 by default. This port should not be accessible from untrusted hosts. Another simple way to check whether or not your MySQL port is open is to try the following command from some remote machine, where server_host is the host name or IP address of the host on which your MySQL server runs:
    shell> telnet server_host 3306
  2. If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If telnet hangs or the connection is refused, the port is blocked, which is how you want it to be.
  3. Do not trust any data entered by users of your applications. They can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like
    "; DROP DATABASE mysql;"

    This is an extreme example, but large security leaks and data loss might occur as a result of hackers using similar techniques, if you do not prepare for them. A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as:

     SELECT * FROM table WHERE ID=234

    when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query:

    SELECT * FROM table WHERE ID=234 OR 1=1

    As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants:

    SELECT * FROM table WHERE ID='234'

    If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.

Continue reading “MySQL General Security Guidelines” »