Set - 4

Question 21 :

MySQL - `--' as the Start of a Comment

Answer :

MySQL doesn't support views, but this is on the TODO.


Question 22 :

MySQL - Views

Answer :

Some other SQL databases use `--' to start comments. MySQL has `#' as the start comment character, even if the mysql command-line tool removes all lines that start with `--'. You can also use the C comment style /* this is a comment */ with MySQL.

MySQL Version 3.23.3 and above supports the `--' comment style only if the comment is followed by a space. This is because this degenerate comment style has caused many problems with automatically generated SQL queries that have used something like the following code, where we automatically insert the value of the payment for !payment!:

UPDATE tbl_name SET credit=credit-!payment!

What do you think will happen when the value of payment is negative?

Because 1--1 is legal in SQL, we think it is terrible that `--' means start comment.

In MySQL Version 3.23 you can, however, use: 1-- This is a comment

The following discussion only concerns you if you are running a MySQL version earlier than Version 3.23:

If you have a SQL program in a text file that contains `--' comments you should use:

shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql database

instead of the usual:

shell> mysql database < text-file-with-funny-comments.sql

You can also edit the command file ``in place'' to change the `--' comments to `#' comments:

shell> replace " --" " #" -- text-file-with-funny-comments.sql

Change them back with this command:

shell> replace " #" " --" -- text-file-with-funny-comments.sql


Question 23 :

MySQL - How to Cope Without COMMIT/ROLLBACK

Answer :

The following mostly applies only for ISAM, MyISAM, and HEAP tables. If you only use transaction-safe tables (BDB tables) in an a update, you can do COMMIT and ROLLBACK also with MySQL.
The problem with handling COMMIT-ROLLBACK efficiently with the above table types would require a completely different table layout than MySQL uses today. The table type would also need extra threads that do automatic cleanups on the tables, and the disk usage would be much higher. This would make these table types about 2-4 times slower than they are today.
For the moment, we prefer implementing the SQL server language (something like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance.
Loops that need transactions normally can be coded with the help of LOCK TABLES, and you don't need cursors when you can update records on the fly.
We at TcX had a greater need for a real fast database than a 100% general database. Whenever we find a way to implement these features without any speed loss, we will probably do it. For the moment, there are many more important things to do. Check the TODO for how we prioritize things at the moment. (Customers with higher levels of support can alter this, so things may be reprioritized.)
The current problem is actually ROLLBACK. Without ROLLBACK, you can do any kind of COMMIT action with LOCK TABLES. To support ROLLBACK with the above table types, MySQL would have to be changed to store all old records that were updated and revert everything back to the starting point if ROLLBACK was issued. For simple cases, this isn't that hard to do (the current isamlog could be used for this purpose), but it would be much more difficult to implement ROLLBACK for ALTER/DROP/CREATE TABLE.
To avoid using ROLLBACK, you can use the following strategy:
Use LOCK TABLES ... to lock all the tables you want to access.
Test conditions.
Update if everything is okay.
Use UNLOCK TABLES to release your locks.
This is usually a much faster method than using transactions with possible ROLLBACKs, although not always. The only situation this solution doesn't handle is when someone kills the threads in the middle of an update. In this case, all locks will be released but some of the updates may not have been executed.

You can also use functions to update records in a single operation. You can get a very efficient application by using the following techniques:

Modify fields relative to their current value.
Update only those fields that actually have changed.
For example, when we are doing updates to some customer information, we update only the customer data that has changed and test only that none of the changed data, or data that depend on the changed data, has changed compared to the original row. The test for changed data is done with the WHERE clause in the UPDATE statement. If the record wasn't updated, we give the client a message: "Some of the data you have changed have been changed by another user". Then we show the old row versus the new row in a window, so the user can decide which version of the customer record he should use.

This gives us something that is similar to column locking but is actually even better, because we only update some of the columns, using values that are relative to their current values. This means that typical UPDATE statements look something like these:

UPDATE tablename SET pay_back=pay_back+'relative change';

UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_he_owes_us=money_he_owes_us+'new_money'
WHERE
customer_id=id AND address='old address' AND phone='old phone';

As you can see, this is very efficient and works even if another client has changed the values in the pay_back or money_he_owes_us columns.

In many cases, users have wanted ROLLBACK and/or LOCK TABLES for the purpose of managing unique identifiers for some tables. This can be handled much more efficiently by using an AUTO_INCREMENT column and either the SQL function LAST_INSERT_ID() or the C API function mysql_insert_id().

At MySQL AB, we have never had any need for row-level locking because we have always been able to code around it. Some cases really need row locking, but they are very few. If you want row-level locking, you can use a flag column in the table and do something like this:

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

MySQL returns 1 for the number of affected rows if the row was found and row_flag wasn't already 1 in the original row.
You can think of it as MySQL changed the above query to:

UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;


Question 24 :

MySQL - General Security

Answer :

Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.

In discussing security, we emphasize the necessity of fully protecting the entire server host (not simply the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.

MySQL uses Access Control Lists (ACLs) security for all connections, queries, and other operations that a user may attempt to perform. There is also some support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.

When running MySQL, follow these guidelines whenever possible:

DON'T EVER GIVE ANYONE (EXCEPT THE MySQL ROOT USER) ACCESS TO THE mysql.user TABLE! The encrypted password is the real password in MySQL. If you know this for one user you can easily login as him if you have access to his 'host'.
Learn the MySQL access privilege system. The GRANT and REVOKE commands are used for restricting access to MySQL. Do not grant any 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, you have problems. Any user (not just root) can connect to your MySQL server with full privileges! Review the MySQL installation instructions, paying particular attention to the item about setting a root password.
Use the command SHOW GRANTS and check to see who has access to what. Remove those privileges that are not necessary using the REVOKE command.
Do not keep any plain-text passwords in your database. When your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead use MD5() or another one-way hashing function.
Do not use passwords from dictionaries. There are special programs to break them. 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 ``Mhall'' which is taken from the first characters of of each word in the sentence ``Mary had a little lamb.'' This is easy to remember and type, but hard to guess for someone who does not know it.
Invest in a firewall. This protects from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ). Checklist:
Try to scan your ports from the Internet using a tool such as nmap. MySQL uses port 3306 by default. This port should be inaccessible from untrusted hosts. Another simple way to check whether or not your MySQL port is open is to type telnet server_host 3306 from some remote machine, where server_host is the hostname of your MySQL server. 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 just hangs, everything is OK, the port is blocked.
Do not trust any data entered by your users. 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 may occur as a result of hackers using similar techniques, if you do not prepare for them. Also remember to check numeric data. A common mistake is to protect only strings. Sometimes people think that if a database contains only publicly available data that it need not be protected. This is incorrect. At least denial-of-service type attacks can be performed on such databases. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants: SELECT * FROM table WHERE ID='234' instead of SELECT * FROM table WHERE ID=234. MySQL automatically converts this string to a number and strips all non-numeric symbols from it. Checklist:
All WWW applications:
Try to enter `'' and `"' in all your Web forms. If you get any kind of MySQL error, investigate the problem right away.
Try to modify any dynamic URLs by adding %22 (`"'), %23 (`#'), and %27 (`'') in the URL.
Try to modify datatypes in dynamic URLs from numeric ones to character ones containing characters from previous examples.
Your application should be safe against this and similar attacks.
Try to enter characters, spaces, and special symbols instead of numbers in numeric fields. Your application should remove them before passing them to MySQL or your application should generate an error. Passing unchecked values to MySQL is very dangerous!
Check data sizes before passing them to MySQL.
Consider having your application connect to the database using a different user name than the one you use for administrative purposes. Do not give your applications any more access privileges than they need.
Users of PHP:
Check out the addslashes() function.
Users of MySQL C API:
Check out the mysql_escape() API call.
Users of MySQL++:
Check out the escape and quote modifiers for query streams.
Users of Perl DBI:
Check out the quote() method.
Do not transmit plain (unencrypted) data over the Internet. These data are accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections as of Version 3.23.9. SSH port-forwarding can be used to create an encrypted (and compressed) tunnel for the communication.
Learn to use the tcpdump and strings utilities. For most cases, you can check whether or not MySQL data streams are unencrypted by issuing a command like the following:
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings

(This works under Linux and should work with small modifications under other systems). Warning: If you do not see data this doesn't always actually mean that it is encrypted. If you need high security, you should consult with a security expert.


Question 25 :

How to Make MySQL Secure Against Crackers ?

Answer :

When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication.
All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in MySQL Version 3.22 and above) to make things much harder. To make things even more secure you should use ssh (see http://www.cs.hut.fi/ssh). With this, you can get an encrypted TCP/IP connection between a MySQL server and a MySQL client.
To make a MySQL system secure, you should strongly consider the following suggestions:
Use passwords for all MySQL users. Remember that anyone can log in as any other person as simply as mysql -u other_user db_name if other_user has no password. It is common behavior with client/server applications that the client may specify any user name. You can change the password of all users by editing the mysql_install_db script before you run it, or only the password for the MySQL root user like this:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;

Don't run the MySQL daemon as the Unix root user. It is very dangerous as any user with FILE privileges will be able to create files as root (for example, ~root/.bashrc). To prevent this mysqld will refuse to run as root unless it is specified directly via --user=root option. mysqld can be run as any user instead. You can also create a new Unix user mysql to make everything even more secure. If you run mysqld as another Unix user, you don't need to change the root user name in the user table, because MySQL user names have nothing to do with Unix user names. You can edit the mysql.server script to start mysqld as another Unix user. Normally this is done with the su command.
If you put a password for the Unix root user in the mysql.server script, make sure this script is readable only by root. Check that the Unix user that mysqld runs as is the only user with read/write privileges in the database directories. On Unix platforms, do not run mysqld as root unless you really need to. Consider creating a user named mysql for that purpose.
Don't give the process privilege to all users. The output of mysqladmin processlist shows the text of the currently executing queries, so any user who is allowed to execute that command might be able to see if another user issues an UPDATE user SET password=PASSWORD('not_secure') query. mysqld reserves an extra connection for users who have the process privilege, so that a MySQL root user can log in and check things even if all normal connections are in use. Don't give the file privilege to all users. Any user that has this privilege can write a file anywhere in the file system with the privileges of the mysqld daemon! To make this a bit safer, all files generated with SELECT ... INTO OUTFILE are readable to everyone, and you can't overwrite existing files. The file privilege may also be used to read any file accessible to the Unix user that the server runs as. This could be abused, for example, by using LOAD DATA to load `/etc/passwd' into a table, which can then be read with SELECT.
If you don't trust your DNS, you should use IP numbers instead of hostnames in the grant tables. In principle, the --secure option to mysqld should make hostnames safe. In any case, you should be very careful about creating grant table entries using hostname values that contain wild cards!
If you want to restrict the number of connections for a single user, you can do this by setting the max_user_connections variable in mysqld.