Category Archives: MySQL

Starting MySQL from the Windows Command Line

Quote of the… night!

The MySQL Server can be started manually from the command line. This can be done on any version of Windows.

To start the mysqld server from the command line, you should start a console window (or “DOS window”) and enter this command:

shell> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld"

The path to mysqld may vary depending on the install location of MySQL on your system.

You can stop the MySQL server by executing this command:

shell> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin" -u root shutdown

If the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted.

This command invokes the MySQL administrative utility mysqladmin to connect to the server and tell it to shut down. The command connects as the MySQL root user, which is the default administrative account in the MySQL grant system. Note that users in the MySQL grant system are wholly independent from any login users under Windows.

If mysqld doesn’t start, check the error log to see whether the server wrote any messages there to indicate the cause of the problem. The error log is located in the C:\Program Files\MySQL\MySQL Server 5.0\data directory. It is the file with a suffix of .err. You can also try to start the server as mysqld –console; in this case, you may get some useful information on the screen that may help solve the problem.

The last option is to start mysqld with the –standalone and –debug options. In this case, mysqld writes a log file C:\mysqld.trace that should contain the reason why mysqld doesn’t start.

Use mysqld –verbose –help to display all the options that mysqld supports.

Command Line = Life Saver!

15 Rules to follow for safer web applications

After a small research and in conclusion I (try to) follow these rules…
(don’t mind the order!)

1. Display custom errors pages.

2. Remove unwanted files and folders.

3. Remove backup, unused or obsolete files (.bak, .inc, .old etc). For include files, carefully choose the suffix to prevent information disclosure.

4. Remove default document files (default.aspx, index.php, index.asp, main.jsp etc).

5. Validate properly all parameters against expected data length, data type, data format (dd-mm-yyyy etc) and data range (10-79 etc).

6. Use SANITIZE functions (custom made).

7. Disable directory browsing. If this is required, make sure the listed files does not induce risks.

8. Remove the private IP address from the HTTP response body. For comments, use jsp/asp comment instead of HTML/javascript comment which can be seen by client browsers.

9. For secure content, put session ID in cookie ,use a combination of cookie and URL rewrite, bind the Session ID to the IP address of the client who owns the session and put timeout mechanisms for the Session IDs against Replay Attacks.

10. Turn off AUTOCOMPLETE attribute in form or individual input elements containing password by using AUTOCOMPLETE=’OFF’.

11. Secure page can be cached in browser. Cache control is not set in HTTP header nor HTML header. Sensitive content can be recovered from browser storage. The best way is to set HTTP header with: ‘Pragma: No-cache’ and ‘Cache-control: No-cache’. Alternatively, this can be set in the HTML header but some browsers may have problem using this method.

12. Do not trust client side input and enforce tight check in the server side. Disable server side include. Use least privilege to run your web server or application server. For Apache, disable the following:

Options Indexes FollowSymLinks Includes
AddType application/x-httpd-cgi .cgi
AddType text/x-server-parsed-html .html

13. Do not trust client side input even if there is client side validation. In general,

  • If the input string is numeric, type check it.
  • If the application used JDBC, use Prepared Statement or Callable Statement with parameters passed by ?
  • If the application used ASP, use ADO Command Objects with strong type checking and parameterized query.
  • If stored procedure or bind variables can be used, use it for parameter passing into query. Do not just concatenate string into query in the stored procedure!
  • Do not create dynamic SQL query by simple string concatenation.
  • Use minimum database user privilege for the application. This does not eliminate SQL injection but minimize its damage. E.g. if the application requires reading one table only, grant such access to the application. Avoid using sa or db-owner or root.

14. Do not try to work with invalid data. Display custom error page when data does not pass validation.

15. Use white list validation wherever possible!

SQL error – Out of range value adjusted for column OR Duplicate entry xxx for key 1

CREATE TABLE test_table (test_id TINYINT, test_text VARCHAR(20));
INSERT INTO test_table (test_id, test_text) VALUES ('', 'This is a test');

SQL error “Out of range value adjusted for column…”

if you have removed STRICT_TRANS_TABLES from my.ini (Windows) or my.cnf (Linux)
Server SQL Modes

You will get this error…
Duplicate entry xxx for key 1

If you want to remove STRICT_TRANS_TABLES
1. You need to have access and restart MySQL Server! A production server !?!?
2. Not everybody has root access or admin rights to change the sql mode.
3. Maybe, you need the server to run in strict mode for some reason.

Check the field type (e.g test_id TINYINT -128 to 127 )
So if you are trying to insert into a table value 128 it won’t insert !

MySQL 5.1 Reference Manual :: 10 Data Types :: 10.2 Numeric Types