A Few MySQL Tips You Might Not Know

Post an Article

Date: 15th Oct 2005
Author: Webmaster
Contact Author
Ranking: 9.67 (3 Votes)

MySQL is a database server suitable for small and a little bigger sized database applications. It has been the all time favourite since its creation. It works on windows as well as Unix / Linux but when run on Unix / Linux, it can provide multithreading which boosts its performance a lot. MySQL supports standards SQL (ANSI) and can be used with a variety of web technologies pretty efficiently. PHP with MySQL is one of the best combinations used by millions of websites and they are happy with it. And the beauty of both PHP and MySQL is that they are open source meaning FREE!! In this article I will describe a few MySQL functions, some of them I had been desperate for back at some point in my web development journey.

Last Inserted Row Number

Often when working with multiple tables, you run an INSERT query on a table and then you need to find out its ID in order to be able to use it to update another table. I used to use the MAX function to perform this until I found this useful function called mysql_insert_id. This function, when given the link identifier, retrieves the AUTO_INCREMENT ID of the last INSERT operation. Below is an example of how it can be used:

	// Insert query for a table with an AUTO_INCREMENT field named order_id
	// the values for the rest of the fields product name, quantity, price
	//are being inserted

	$ins_qry = "INSERT INTO orders values (‘Umbrella','2','50')";

	//Run the query myConn represents the connetion string

	$qryresource = mysql_query($ins_qry, $myConn);

	// Get the inserted ID

	$inserted_id = mysql_insert_id($myConn);

	// $inserted_id now contains the id for the last inserted row

NOTE: If the link identifier is not specified, the last link opened by mysql_connect() is assumed.

FULLTEXT Search

When working with searches on text fields in MySQL, the LIKE queries are very commonly used. The situation gets complicated when it comes to using them on large tables. MySQL now provides an alternative, which is easy to use and more efficient. The FULLTEXT can create indexes of CHAR, VARCHAR and TEXT fields and organise them efficiently. It can also return a relevance value based on how well each result matched the words a user is looking for. To CREATE a table and ask MySQL to start indexing a field or set of fields you would write something like this:

CREATE TABLE articles (
    ->   article_id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
	->   FULLTEXT (title,body)

You can also ALTER an existing table to start building an index of some of the columns:

	ALTER TABLE articles ADD FULLTEXT art_search (title, body)

You will then need to INSERT or MODIFY a record to get MySQL start indexing it. With the FULLTEXT indexing turned on you can use the MATCH() function to search for a string against the columns included. By default the search is not case-sensitive but you can change it by using a binary collation. The following SELECT statement will return all indexes in the title and body columns against the word "MySQL database", along with the relevance score of each result:

	SELECT id, body, MATCH (title,body) AGAINST
		-> ('MySQL Database') AS score
		-> FROM articles WHERE MATCH (title,body) AGAINST
		-> ('MySQL Database');

The MySQL FULLTEXT implementation regards any sequence of true word characters (letters, digits, and underscores) as a word. That sequence may also contain apostrophes ('), but not more than one in a row.

For more reading on the FULLTEXT features please refer to the MySQL manual at http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Stop Injection Attacks

The user input mechanism in online forms and the way it is handled by the code has a potential for abuse. This techniques used by the hackers is called SQL Injection Attacks. The attacks can be more dangerous if you are using a database other than MySQL. The reason being MySQL does not allow more than one statemnts in a single call. The attack can cause serious problems in some situations like online authentication systems if not handled properly. In a typical example of authenticating a user through a provided Login ID and Password, you would normally have something like this:

	// userid and pwd are the field names posted by the user

	SELECT * from users WHERE
	userid='" . $_POST['userid'] . "'
	AND
	password='". $_POST['pwd'] ."'

	// Get the results and check if any match is found
	// Authenticate the user if match is found

Using the SQL Injection Attack a hacker can enter:

	OR userid LIKE '%'

Taking this value as an input, your query will become:

	SELECT * from users
	WHERE userid=' ' AND password=' ' OR username LIKE '%'

This will cause the above query to select all records from the table resulting in authenticating the user.

To avoid this problem you need to escape the incoming variables as shown below:

	SELECT * from users
	WHERE userid='" . safeEscapeString($_POST['userid']) . "'
	AND
	password='". safeEscapeString($_POST['pwd']) ."'

I am sure this would only cost you a little more time but trust me its worth applying it anywhere you get data from users using a web interface.

For further reading on this topic please refer to the following URLs

http://www.sitepoint.com/article/sql-injection-attacks-safe
http://www.unixwiz.net/techtips/sql-injection.html

---

Comments

No comment has been posted for this article yet. Be the first to comment below