Prodromus

Determining the number of tables that are contained in a MySQL database is very straight-forward, although it is an often asked question. The simplest way to accomplish this is using the following SQL query. In this query, you will provide the database, and the SQL will access MySQL’s internal data scheme (information_schema).

SELECT count(*) as ‘Tables’, table_schema as ‘Database’
FROM information_schema.TABLES
WHERE table_schema= ‘The Database Name’
GROUP BY table_schema

If you need to retrieve this information using PHP, use can use the following code. It creates a connection, runs the query, and returns the number of rows retrieved. This query you notice is slightly different than the one above, often there are multiple ways to get the same result!

$conn = mysql_connect(‘localhost’, ‘USERNAME’, ‘PASSWORD’, 1, 65536);
$res = mysql_query( “select table_name from information_schema.tables where table_schema=’test’”, $conn );
echo mysql_num_rows( $res );

And lastly, there is one more way that you can retrieve the number of tables in a database using PHP and the Show Tables command. In this example, you will need to be connected to a server, and have the database set to the one you are querying against.

echo “<pre>”;
$tbl_List = mysql_query(“SHOW TABLES”);
$i=0;
while($tables = mysql_tablename($tbl_List,$i))
{

echo $tables;

$i++;
}
echo “<br />Table count = $i”;
echo “</pre>”;

Related articles

• Tmobile Comet (neuphones.com)
• Bell P-39N Airacobra – Little Sir Echo – Small Fry (classic-machines.com)
• Topgun Days by Dave “Bio” Baranek – The Truth Behind Topgun and Top Gun (classic-machines.com)

Bobby Tables and the lesson he teaches

SQL injection vulnerabilities are often been described as the most serious threat for Web applications, regardless of what language they are written in . Web applications that are vulnerable to SQL injection may allow an attacker to gain complete access to their underlying databases, regardless of if they are using MySQL Server, SQL Server, or Oracle.

SQL-injection attacks are those in which data provided by the user is included in an SQL query in such a way that part of the user’s input is treated as SQL code that is executed on the server.   By using this technique, an attacker can submit SQL commands directly to the database.   These attacks are a serious threat to any Web application that receives input from users and passes it into SQL queries to an underlying database server.  If  user input is not santised properly, web applications may result in SQL Injection attacks that allow hackers to view information from the database and/or even wipe it out.

To defend against SQL Injection attacks, user input must not directly be embedded into SQL statements that are executed on the server.  Instead, you must use parameterized statements, and Escaping functions to check user input.

Various resource for addressing SQL Injection are as follows:

Bobby Tables provides real-world practical code for addressing SQL Injection - http://bobby-tables.com/
Michal Daw’s Blog Page outlines various SQL Injection vectors - http://michaeldaw.org/sql-injection-cheat-sheet