How to achieve safe updates with MySQL in PHP

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();

?>