Web Programming with PHP

22 Accessing a Database

  1. mysql_connect(): open a connection to a MySQL Server and return a link_identifier.
  2. mysql_select_db(): select a MySQL database.
  3. mysql_query(): send a MySQL query
  4. Fetch a result:
    • mysql_fetch_array(): fetch a result row as an associative array, a numeric array, or both.
    • mysql_fetch_assoc(): fetch a result row as an associative array.
    • mysql_fetch_row(): get a result row as an enumerated array.
  5. mysql_free_result(): free result memory.
  6. mysql_close(): close MySQL connection.

Most MySQL functions accept link_identifier as the last optional parameter. If it is not provided, last opened connection is used.

Before using users' input data in a SQL query, input data must be validated. If not, you will have this type of problems:

Exploits of a mom
Source: Explits of a mom

Explanation: Her son's name is a reference to SQL injection attacks, cyber attacks which rely on data being passed to a database server without checking for malicious input (a process known as sanitizing). In this case, the school entering her sons name Robert'); DROP TABLE Students;-- caused the database to drop, or delete its table of students.

Many SQL injection attacks exploit flawed permissions set for the database users. The user connecting to the database from the PHP application should has permission only on the table or tabless they need, and more importantly, the user should not have permission to access the tables of the system (information_schema and mysql). These tables are the heart of a database management system and access to it will give any intruder the names of all tables and columns in the database.