Linux, MySQL, PhP, Perl…

A simple PHP function for INPUT validation.

function check_chars($variable,$what,$min_length, $max_length,$bad_chars){ 
	if (strlen($variable) < $min_length || strlen($variable)  > $max_length) {
		die("$what is not the correct number of chars or is missing.");
	} else if (preg_match("/$bad_chars/i", $variable)) {
		die("Incorrect chars in $what.");
	} else {
		echo "Successful type constraint check for $what.<br \>";
	}
 
	$variable= mysql_real_escape_string($variable); //a good place to escape anything suspicious that might have left.
	return $variable;
}
 
//example usage
 
check_chars($first_name,'First name',2,25,"[^a-z]"); //names should be longer than 2 and less than 25 chars
check_chars($middle_initial,'Middle name',1,1,"[^a-z]"); //we want only the first char from the name
check_chars($address,'Address',2,100,"[^a-z0-9-, ]"); //this could be probably improved
check_chars($city,'City',2,25,"[^s-zA-Z]"); 
check_chars($state,'State',2,2,"[^A-Z]");
check_chars($zip,'Zip',5,5,"[^0-9]");

Database normalization prevents redundancy, inconsistency and data loss. It includes several steps as follows:

1. 1NF - First Normal Form states that all tuple values should be atomic. Atomic means that there should be only one useful piece of data for each attribute.

The first step in database normalization is to determine the functional dependencies in the database. This means to find which values are determined by which.

2. Decomposition and Boyce/Codd Normal Form

BCNF has only one requirement - every functional dependency must be functionally determined by either a candidate key*, or a superset of a candidate key( superkey).

*A candidate key is a set of attributes that must be unique for each tuple and irreducible into a smaller key.

Example:

candidate_key -> value

candidate_key2 -> value2

...

5. 5th Normal Form requires that each join dependency is satisfied by superkeys.

A join dependency(JD) is a set of projections on a relation which when joined reform the original relation.

The difference between relvar and relation:

A relation is a value, filled with all the attribute and tuple values. On the other hand a relvar is a variable associated with some representation of a relation.

The difference between a base relation and a view:

Base relation is the original value while a view is deducted from it by a certain criteria (select statement).

If for some strange reason you have to import CSV file directly into MySQL here is a good query to go:

mysql> LOAD DATA INFILE 'file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (column1, column2);

It gives you all the options such as how fields are terminated, enclosed and so on. The above example is specific for a csv file created with a Windows Text Editor. (windows... haha)

Similarly, if you have to export a query into a text file here is how this can be done:

mysql> SELECT column1, column2 INTO OUTFILE 'file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\r\n' FROM table_name;

MySQL privileges assignment is a complex process which allows you to set different privileges for an user for different tables/databases. However, this process is very often neglected and users are granted with all privileges for a whole database.

If improper privileges are applied this can lead to a website being hacked via MySQL. This means that the attacker executes a MySQL query which either illegally retrieves, updates or inserts information.

Thus when you manually assign privileges make sure not to grant only the needed privileges to the corresponding tables. This is not always possible though since many popular web applications use just one user for everything.
So here is the usual MySQL privileges granting:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON database.* TO user@localhost IDENTIFIED BY 'password';

This will grant all privileges to user@localhost. However, imagine that you can divide your script into parts with different functionality. For example, user_logs would be used to read just the logs from the 'logs' table. Then his privileges should be:

GRANT SELECT ON database.logs TO user_logs@localhost IDENTIFIED BY 'password';

This will make sure that even compromised user_logs will not be able to mess with the entire database.
MySQL privileges assignment is a complex process which allows you to set different privileges for an user for different tables/databases. However, this process is very often neglected and users are granted with all privileges for a whole database.

If improper privileges are applied this can lead to a website being hacked via MySQL. This means that the attacker executes a MySQL query which either illegally retrieves, updates or inserts information.

Thus when you manually assign privileges make sure not to grant only the needed privileges to the corresponding tables. This is not always possible though since many popular web applications use just one user for everything.

So here is the usual MySQL privileges granting:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON database.* TO user@localhost IDENTIFIED BY 'password';

This will grant all privileges to user@localhost. However, imagine that you can divide your script into parts with different functionality. For example, user_logs would be used to read just the logs from the 'logs' table. Then his privileges should be:

GRANT SELECT ON database.logs TO user_logs@localhost IDENTIFIED BY 'password';

This will make sure that even compromised user_logs will not be able to mess with the entire database.