How to achieve safe updates with MySQL in PHP

How to achieve safe updates with MySQL in PHP

Posted using ShareThis

How to achieve safe updates with MySQL and PHP

In this post, we are going to discuss about safety updates with MySQL and using that with PHP.

For beginners, a useful startup option is ’safe updates’ (or –i-am-a-dummy, which has the same effect).

This option was introduced in MySQL 3.23.11. It is helpful during situations wherein you might have issued a “DELETE FROM tbl_name” statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With ’safe updates’, you can delete rows only by specifying the key values that identify them. Hence, this helps prevent accidental deletions.

When you use the ’safe updates’ option, MySQL issues the following statement when it connects to the MySQL server:

SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;

The SET statement has the following effects:

You are not allowed to execute an UPDATE or DELETE a statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both). For example:

UPDATE tbl_name SET not_key_column=val WHERE key_column=val;

UPDATE tbl_name SET not_key_column=val LIMIT 1

The server limits all large SELECT results to 1,000 rows unless the statement includes a LIMIT clause.

These are the options available with MySQL.

Now, we can use this in our coding by doing a small trick.

When you use the ’safe updates’ option and connect MySQL at command prompt, MySQL issues the following statement when it connects to the MySQL server:

SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;’

So, we can use this “SET sql_safe_updates=1″ query in our PHP coding. After connecting with the server and selecting the db, we need to execute this query so that safety update will affect all the other queries executed thereafter. This will ensure that no updates or delete operations perform without the WHERE clause.

This is an useful and important safety measure which can be used in our projects to avoid accidental deletion of all records in a table.

<?
// This is will be useful to avoid sql injection which may delete all rows of a table

// http://dev.mysql.com/doc/refman/4.1/en/mysql-tips.html

error_reporting(E_ALL);

$con= mysql_connect(”localhost”,”sorna”,”password”);
mysql_select_db(”test1″,$con);
mysql_query(”SET sql_safe_updates=1″);

mysql_query(”DELETE FROM register”); // This line won’t delete the table since we have turned on the safe updates mode. It won’t execute the delete query when it doesn’t have where clause
mysql_query(”DELETE FROM register WHERE id=6″); // This line will delete the record in which id is 6
mysql_close();

?>

Source: http://www.dotcominfoway.com/blog

Whitelist Form for SPAM Protection: Part II

Whitelist Form for SPAM Protection: Part II

Posted using ShareThis

This post is a continuation of our previous post
Whitelist Form for SPAM Protection: Part I

Here, we have explained the session part mentioned in the code.

Please refer to code samples before moving to the explanation.

Cross-site scripting (XSS) allows code injection of harmful web users in the web pages used by other users. Attackers can do it by using client-side scripts which help them to exploit browser details.

Attackers mainly use this method to hack a site when users browse/enter sensitive data like username, password, bank account number etc. Everything seems fine to the end-user while entering crucial data, but they maybe subject to unauthorized access i.e. they might become a victim of hacking, whereby all their important data are given away to the hackers. This leads to financial and critical data loss.

Here is a solution called Session which can circumvent such hacking problems. Session code is set on the server-side. We can generate a random session key by using md5 for encryption so that the session code is never repeated and stands unique. This code is set as a hidden value in the page which is being browsed by the user. Session codes are never the same. They keep changing with the browser.

This session code can be submitted through URL or as a hidden field. As shown in the code, we check for the session code generated and submitted from the key session. If the person trying to hack the site tries to manipulate user data and submit it, the session code will be different or there will not be any session code. From this, we can check for user intrusion/hacking and stop such hackers from proceeding further.

Using session not only prevents intrusion, but also theft of crucial data by attackers/hackers. Above all, it ensures complete security to end-users.

Source: http://www.dotcominfoway.com/blog

How To Set Up Database Replication In MySQL

How To Set Up Database Replication In MySQL

How To Set Up Database Replication In MySQL

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

In this tutorial I will show how to replicate the database exampledb from the master with the IP address 192.168.0.100 to a slave. Both systems (master and slave) are running Debian Sarge; however, the configuration should apply to almost all distributions with little or no modification.

Both systems have MySQL installed, and the database exampledb with tables and data is already existing on the master, but not on the slave.

I want to say firstthat this is not the only way of setting up such a system. There are many waysof achieving this goal but this is the way I take. I do not issue any guaranteethat this will work for you!

1 Configure The Master

First we have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):

#skip-networking #bind-address = 127.0.0.1

Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf:

log-bin = /var/log/mysql/mysql-bin.log binlog-do-db=exampledb server-id=1

Then we restart MySQL:

/etc/init.d/mysql restart

Then we log into the MySQL database as root and create a user with replication privileges:

mysql -u root -p
Enter password:

Now we are on the MySQL shell.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The last command will show something like this:

+---------------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+------------------+ | mysql-bin.006 | 183 | exampledb | | +---------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

Write down this information, we will need it later on the slave!

Then leave the MySQL shell:

quit;

---

There are two possibilities to get the existing tables and data from exampledb from the master to the slave. The first one is to make a database dump, the second one is to use the LOAD DATA FROM MASTER; command on the slave. The latter has the disadvantage the the database on the master will be locked during this operation, so if you have a large database on a high-traffic production system, this is not what you want, and I recommend to follow the first method in this case. However, the latter method is very fast, so I will describe both here.

If you want to follow the first method, then do this:

mysqldump -u root -p<password> --opt exampledb > exampledb.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)

This will create an SQL dump of exampledb in the file exampledb.sql. Transfer this file to your slave server!

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.

---

Finally we have to unlock the tables in exampledb:

mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;

Now the configuration on the master is finished. On to the slave...

2 Configure The Slave

On the slave we first have to create the database exampledb:

mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;

---

If you have made an SQL dump of exampledb on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created exampledb on the slave:

mysql -u root -p<password> exampledb < /path/to/exampledb.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.

---

Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the master database to watch is exampledb. Therefore we add the following lines to /etc/mysql/my.cnf:

server-id=2 master-host=192.168.0.100 master-user=slave_user master-password=secret master-connect-retry=60 replicate-d-db=exampledb

Then we restart MySQL:

/etc/init.d/mysql restart

---

If you have not imported the master exampledb with the help of an SQL dump, but want to go the LOAD DATA FROM MASTER; way, then it is time for you now to get the data from the master exampledb:

mysql -u root -p
Enter password:
LOAD DATA FROM MASTER;
quit;

If you have phpMyAdmin installed on the slave you can now check if all tables/data from the masterexampledb is also available on the slave exampledb.

---

Finally, we must do this:

mysql -u root -p
Enter password:
SLAVE STOP;

In the next command (still on the MySQL shell) you have to replace the values appropriately:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

• MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
• MASTER_USER is the user we granted replication privileges on the master.
• MASTER_PASSWORD is the password of MASTER_USER on the master.
• MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
• MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Now all that is left to do is start the slave. Still on the MySQL shell we run

START SLAVE;
quit;

That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!

Links

MySQL:http://www.mysql.com

Source: http://www.howtoforge.com/mysql_database_replication

Advanced MySQL Replication Techniques - O'Reilly Media

Advanced MySQL Replication Techniques - O'Reilly Media

Posted using ShareThis

CAPTCHA and proposed alternative, SAPTCHA

Introduction

(skip to next section if you are familiar with concept of CAPTCHA)

CAPTCHA stands for Completely Automated Public Turing Test to Tell Computers and Humans Apart. [Wikipedia / Captcha]

Simply put, CAPTCHA is a set of methods commonly used to block automated account registration and similar massive abuse by making it costlier to spammer. Most common type of CAPTCHA is visual CAPTCHAs that test for image recognition. Though, at current moment, computers (using good software) is no worse than humans at single character image recognition (source) (fortunately spammers don't bother to use such software yet.)

Most likely you have already been tested by CAPTCHAs - that's those images od distorted and obstructed letters that you must enter into text field to complete registration of email account or to post reply to blog.

Verbal CAPTCHA would not discriminate against vision impaired, but computer will only be able to generate very limited subset of questions and thus it would be relatively simple to defeat such CAPTCHA. Audio CAPTCHAs is uncommon because one would still need visual one and it would double effort.



CAPTCHAs has numerous problems(see wikipedia article linked above for good overview); there is existing methods of character recognition, plus it is often possible to defeat captcha knowing the algorithm it uses.

Intuitively, while computers is not smart enough to pass true Turing test, computers may be smart enough to fool other computers.

In some CAPTCHAs, the image is obscured in a way that makes it harder to read for human, but will have no effect on computer - for example, computer won't have any problem at all filtering out colored background, but it can confuse human(especially colorblind).

Often, human don't know how many letters should be there, and random lines may look like yet another distorted letter, confusing human but not computer that knows how many letters should be there. Some letters in common fonts differ too little to be reliably recognized by human when distorted (such as 0,O ; I,l,i,!,j ; vv,w and so on). Humans recognize heavily distorted letters in handwriting based on the context, but letters in CAPTCHAs lack context.Last but not least, such methods unnecessarily discriminate against disabled who can not see the image.

SAPTCHA.

SAPTCHA stands for Semi Automatic Public Turing Test to Tell Computers and Humans Apart.

The key concept is same as with CAPTCHA: user is presented with test question or instructions and must give correct answer to use resource. Main difference is that computer does not try to automatically generate "unique" test questions on each query; only verification of answer is automatic. Instead, unique test question and answer[s] is set by moderator or owner when SAPTCHA is installed, and should be easy to change if needed.

SAPTCHA is proposed as more accessible alternative to CAPTCHA that may replace CAPTCHA in services such as most blogs and forums. SAPTCHA works as lightweight CAPTCHA.

The concept follows from observation that there is many cases where automated generation of unique test question or image does not add much to prevention of abuse - spammer do not need to pass test more than once on same forum or blog anyway. Often, there's no human spammer interacting with website at all [who wouldn't love to think that his site is so important that it is spammed personally :-)]; in such cases static question is not worse at stopping bot than dynamic. Human generated questions has much broader diversity and is thus harder for computer to answer. It must be also noted that CAPTCHA itself is not really "completely automatic" - human has to write and maintain test software, which will not change often but is costly to develop.

Example questions: User is given instruction like "write [no i'm not a computer!] in this text field" or "write 'i'm human' in reverse" or "write[or copy-paste] web address of this page there" (please don't use too similar things. No default questions and answers. Think up something yourself. Don't try to be clever. It should be not more complex to understand and do than rest of registration instruction and resource usage, and thus shouldn't decrease website's accessability(!). It's better if answer is more than 1 character long or if there is delay or block for bots that "try again".)

Bots can try to understand text written by human in normal language (very hard problem in AI) or try to guess (some delay can make it pointless) or try some common test answers if any (but the common test questions and answers will quickly disappear)

Spammer have to manually answer the question to start spamming. This is exactly same problem as with CAPTCHA at registration. Similarly to CAPTCHA at registration, human invervention is necessary to stop spam. - account must be banned and for SAPCHA question must be changed(if bot can reuse answer automatically).

In a way, SAPTCHA can be viewed as light weight disposable CAPTCHA test that is cheap to replace when it get compromised.

Comparison

Sample use scenarios

SAPTCHA

s.0) Normal user comes accross your blog. If he can answer question, he can post reply, unless you made bad question/instructions. If user can't read your question, probably he can't read your blog either, so the SAPTCHA shouldn't make it less accessible.

s.1) Spammer bot comes accross your blog. No spamming happens. Bots can't understand human language yet.

s.2) Spammer human comes accross your blog/forum, answer question, register account, and possibly add answer and account to spambot database or proceeds to spam manually. You are spammed. It will take a moderator to ban spammer and stop spam; the banning form may also ask moderator for new question and new answer that needs to be provided if spamming was done by bot that "knows" answer to question.

CAPTCHA

s.0) Normal user comes accross your blog/forum. If he can see, and CAPTCHA is simple he can post reply with small hastle if he doesn't have to pass CAPTCHA every time he replies. If CAPTCHA is "unbreakable" or uses bad colors, he will need few tries and is going to get annoyed, especially so if he need to pass it for every reply. If he is blind or otherwise can't see it, no way.

s.1) Spammer bot comes accross your blog. You might get spammed if bot can recognize image (it is possible if you are using popular CAPTCHA), but most likely you won't.

s.2) Spammer human comes accross your blog/forum. He can answer question, register account, add it to spambot database. You are spammed. It will take moderator to ban the bot, and delete spam[assuming that spam filters alone don't suffice without CAPTCHA]; so you still need human intervention from your side. As have been said before, if you'd ask to pass CAPTCHA for every message it'd be too annoying for normal users as well.

Comparison of SAPTCHA versus CAPTCHA features

Advantages of SAPTCHA over CAPTCHA:

1. SAPTCHA software is much easier to implement than CAPTCHA
2. Textual SAPTCHA does not discriminate against disabled who can use internet. [Audio CAPTCHA plus visual CAPTCHA would double effort and is thus very uncommon in practice]
3. There is methods for breaking image based CAPTCHAs. If you use popular CAPTCHA, you may still get spammed by entirely automatic bot. SAPTCHAs can be much more varied and there won't be common method of breaking until it becomes possible for computers to interpret human instructions in normal human language.

Advantages of CAPTCHA over SAPTCHA (disadvantages of SAPTCHA):

1. With SAPTCHA, when banning spammer, moderator must enter new question and answer. With CAPTCHA, though, there's point 1 above (& CAPTCHA code won't remain useful forever either), so for not extremely popular websites it seems highly unlikely that even in long run CAPTCHA would save work.
2. If SAPTCHA is used to protect registration, it is easier to register many accounts at once than with CAPTCHA; may matter with popular email services.
3. Verbal SAPTCHA is problematic when it is multi-language resource that needs frequent changes.
4. When it is something like photo gallery, visual CAPTCHA is allright as it doesn't contribute to inaccessability.

Conclusion:

SAPTCHA can be viable alternative to CAPTCHA for web resources like forums and blogs and in other situations when spammer can not afford to target resources individually. With textual resources, SAPTCHA does not lessen accessability of resource.

It is suggested that forum and blogging software should offer support for SAPTCHA in addition to existing support for CAPTCHA, thus allowing administrator to use SAPTCHA and switch to CAPTCHA only when and if SAPTCHA is found to be really inadequate in this situation (which is expected to happen only on very popular web resources). By the method of operation, SAPTCHA can give only limited protection against account registration abuses when abuser is willing to solve SAPTCHA and consequently run bot that register really many accounts (e.g. for use of email as storage), which would be prevented by CAPTCHA on every registration.

Live example of question

John had one thousand apples and five oranges. He ate as many of his apples as there is letters in word "apple". Also he ate two bananas :-). How many appl es John have?

Your answer:

If you are annoyed by CAPTCHA, think about alternatives and discuss concept of SAPTCHA with others. Make the best meme win.

Source: http://dmytry.pandromeda.com/