Category all articles on using and creating SQL databases
If you have two tables which have columns with should contain the same data, and you want to check that the columns are the same, then it is possible compare the two columns using SQL.
If table1 has a column called users and table2 has a column called username, then to compare those two columns use something like :
SELECT users FROM table1 WHERE username NOT IN (SELECT username from table2)
An article of SQL Database Normalization and Design Techniques which presents the concepts quite well:
http://www.phpbuilder.com/columns/barry20000731.php3?page=1
You can see the process list, find out who is doing what and kill a process if needed.
If you log into PHPmyAdmin and enter the following into the SQL text box :
show processlist;
You will see a list of the processes.
If you are using the mysql command line environment then you can do it at the command line as well
mysql> show processlist;
Once you know the process ID you can then kill the process using the ID number as follows :
mysql>kill 349
Here are some further functions that may be of interest :
show status;
show table status like ‘%’;
The above gives you create time and other information.
All can be run from within PHPmyAdmin or from the mysql command line.
This mySQL “Insert from another table with select” example shows how you can concatenate two of the selected fields into one inserted field.
In this example we are inserting data into tbllistings using data from tblrawdata but we are doing other processing on fields before it is inserted. Obviously you can use any of the other mySQL select string, arithmetic and mathematical operators and functions when you do your select statement and then insert those into the new table.
INSERT INTO tbllistings (
property_id,
agent_id,
property_address,
property_borough,
.. etc
)
SELECT
Prop_ID,
concat(property_id, agent_id),
prop_name,
prop_street,
.. etc
FROM tblrawdata;
<?php
// A simple transaction example using PHP and mysql_query
//——————————————–
@mysql_connect(“localhost”,”myusername”, “mypassword”) or die(mysql_error());
@mysql_select_db(“mydatabase”) or die(mysql_error());
$query = “INSERT INTO mytable (firstname, lastname) values (‘Fred’,’Bloggs’)”;
begin(); // begin transaction
$result = @mysql_query($query);
if(!$result) {
rollback(); // failed so roll back transaction
echo “Rolled back”;
exit;
}
else
{
commit(); // Committ transaction
echo “Transaction completed”;
}
//——————————————–
//——————————————–
function begin() {
@mysql_query(“BEGIN”);
}
//——————————————–
function commit() {
@mysql_query(“COMMIT”);
}
//——————————————–
function rollback() {
@mysql_query(“ROLLBACK”);
}
?>
The above code illustrates the use of transactions with mysql_query code.
A transaction is a collection of SQL statements. So a task may, for example, consist of a number of update statements. If one of these should fail you may end up with some tables correctly updated but others not correctly updated particularly if one update has data from a previous update.
To overcome this you define the SQL statements as a transaction. If one SQL statement should fail, you roll back the data to its initial state. Only when all the statements are successful will you commit the transaction.
When you are entering SQL statements into mySQL you may use the begin / commit / rollback transaction commands.
So, say we are working with a person table and do an INSERT statement, we can either commit or rollback the transaction.
The problem with phpMyAdmin is that all the lines have to be in one command so it is not easy to demonstrate.
However, we can demonstrate it using the following SQL statements and check the results for each.
Copy the following lines in to the SQL editor in phpMyAdmin:
begin;
INSERT INTO person (firstname, lastname) VALUES (“fred”, “blogs”);
commit;
Examine the person table. We should see that a new record has been created.
Now copy the following lines into the SQL editor in phpMyAdmin:
begin;
INSERT INTO person (firstname, lastname) VALUES (“fred”, “blogs”);
rollback;
In this case the transaction has been rolled back and the record has not been created.
If you have the situation in mySQL where there is the possibility of large number of connections, you can test for that specific error and then redirect to an different page :
<?php $link = mysql_connect("localhost", "mysql_user",
"mysql_password");
if (mysql_errno() == 1203) {
// 1203 == ER_TOO_MANY_USER_CONNECTIONS (mysqld_error.h)
header("Location: http://your.site.com/alternate_page.php");
exit;
}
?>
Here is quite a useful function for producing or saving only the date part of the smalldatetime datatype.
This can be useful if you only need to store the date or you need to group some results by date only.
SelectConvert([smalldatetime],floor(Convert([float],getdate(),(0))),(0))
Database Permissions
Set the permissions on the database username / password as tightly as possible. If you are displaying data, there is no need for the user to have insert or update permissions into the database. One solution is to have two usernames / passwords. One would have select permissions, and would be used only for display. The other would have select, insert and update permissions used only for forms that require data to be stored in the database.
Test all data input
All form data and all url query strings should be tested.
For example, if you are passing data using a query string any record id’s are usually integer, so test that they are actually integer values with a function such as is_numeric in PHP.
Use correct data types and data sizes in the database
This means that if you have a colunn which is a persons name, the data type size for that column only needs to be 40 characters. There is no need to have a data size any larger than required.
Convert text to html
Before storing text in a database, convert it into html. This will change inputs such as the Javascript <script> to its html equilivant which cannot be executed on a web page.
Filter out any characters that may cause issues. and are not required.
Use parameterized queries
If you use parametized queries for connection to the database you eliminate string concatenation. You should always use parametized queries rather than constucting the sql.
Check characters particlarly with username / password
If an entry is a username, it normally does not require any other characters other than a to z and 0 to 9 and it only needs to be say, 8 characters long.
In php, always use the mysql_real_escape_string
http://uk3.php.net/mysql_real_escape_string
SQL Injection pdf
http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf
Description of paramised queries and slq injection in dot.net
http://msdn.microsoft.com/en-us/library/ms998271.aspx
Description of paramised queries and slq injection for php
http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php
The only way to reset the admin password is to do a SQL update statement on the database.
You will need to go into phpmyadmin or a similar program which can be user to administer the database tables.
The update statement that you can use is :
UPDATE ipn_tblpasswords SET userpassword = md5(‘admin’) WHERE username = ‘admin’
In this case it is assumed that the username is ‘admin’