Definition And Explaination:-
ALTER TABLE allows you to change the structure of an existing table. For example, you can add or delete columns, create indexes, change the type of existing columns, or rename columns or the table itself.You can use ALTER TABLE to modify the structure of a table that has not been added to a database.Alter table command allows
* To add the field;
* To delete the field out;
* To change the DEFAULT value for any of the field;
* To add or delete the table PRIMARY KEY;
* To add or delete the table FOREIGN KEY;
* To add or delete UNIQUE constraint;
* To add or delete CHECK constraint;
* To rename fields or table itself;
* To change field type.
| ALTER [ COLUMN ] column_name < SET DEFAULT default_option | DROP DEFAULT>
| DROP [ COLUMN ] column_name [ drop_behavior ]
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP CONSTRAINT IDENT
This is a very basic example of php/mysql.We used to make use of mysql database and a php file for getting this done.To run this code you can first create database as provided in sql here and after creating database run this code.Make modifications as you want to do in it.
<html>
<head>
<title>Phone Book Example</title>
</head>
<body bgcolor=”#f6f6f6″ style=”font-family:arial”>
<marquee behavior=”alternate”>
<font size=’7′>Welcome to Phone Book Demo</font>
</marquee>
<br/><hr/><br/>
<table border=”1″ align=”center” width=”100%”>
<tr>
<td>
<h2 align=”center”>Enter Details</h2>
<table border=”1″ align=”center”>
<form action=”phonebookDemo.php” method=”post”>
<tr><td>First Name:</td><td><input type=”text” name=”FirstName” /></td></tr>
<tr><td>Last Name:</td><td><input type=”text” name=”LastName” /></td></tr>
<tr><td>Phone Number:</td><td><input type=”text” name=”PhoneNumber” /></td></tr>
<tr><td colspan=”2″ align=”center”><input type=”submit” value=”Add Contact” /></td></tr>
</form>
</table>
</td>
<td>
<?php
//Connectivity with MySql
$con=mysql_connect(”localhost”,”root”,”");
if(!$con)
{
echo “You are unable to connect with server</br>”;
die(”Error : “.mysql_error());
}
//Connectivity with database in MySql.
mysql_select_db(”phonebook”,$con);
$firstname = $_REQUEST['FirstName'];
$lastname = $_REQUEST['LastName'];
$phone = $_REQUEST['PhoneNumber'];
if($firstname != “” )
{
@mysql_query(”INSERT INTO contacts (FirstName,LastName,PhoneNumber) VALUES(’”.mysql_escape_string($firstname).”‘, ‘”.mysql_escape_string($lastname).”‘, ‘”.mysql_escape_string($phone).”‘)”);
$msg=”Record Added in phonebook”;
}
if($_REQUEST['action']==”del”)
{
@mysql_query(”DELETE FROM contacts WHERE id=”.intval($_REQUEST['id']).”;”);
}
$result = mysql_query(”SELECT * FROM contacts”);
echo “<table border=’1′ align=’center’ cellpadding=’5′>”;
echo “<tr><th>First Name</th>
<th>Last Name</th>
<th>Phone Number</th>
<th>Action</th>”;
if(!mysql_num_rows($result))echo “<tr><td colspan=4>No entry in phonebook</td></tr>”;
if($result)
{
while($row = mysql_fetch_array($result))
{
echo “<tr>”;
echo “<td>”.$row['FirstName'].”</td>”;
echo “<td>”.$row['LastName'].”</td>”;
echo “<td>”.$row['PhoneNumber'].”</td>”;
echo “<td><a onclick=\”return confirm(’Are u sure to delete this entry?’);\” href=’phonebookDemo.php?action=del&id=”.$row['id'].”‘>Delete</a></td>”;
echo “</tr>”;
}
}
echo “</table>”;
//mysql_query(”DELETE FROM contacts WHERE FirstName=’Kuldeep’”);
mysql_query($sql,$con);
mysql_close($con);
?>
</td>
</tr>
</table>
</body>
</html>
SQl for This exampple:-
CREATE TABLE `contacts` (
`id` int(10) NOT NULL auto_increment,
`FirstName` varchar(200) NOT NULL,
`LastName` varchar(200) NOT NULL,
`PhoneNumber` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
–
– Dumping data for table `contacts`
–
INSERT INTO `contacts` (`id`, `FirstName`, `LastName`, `PhoneNumber`) VALUES (3, ‘Inder’, ‘Singh’, ‘98726-82279′),
(5, ‘Inderweb’, ‘India’, ‘98726-82279′);
A Mysql injection is attack tried by site visitors/users to get/damage data in databases by taking benefit from poor programming of websites.An injection attack occurs when a visitor to your site types something into a form input with the purpose of changing the outcome of your MySQL query. For example, at a login screen someone may try this type of attack to gain access to a secure area of the website.
If your query to check the username and password entered by the user was this:
“SELECT * FROM users WHERE username = ‘”.$_POST['username'].”‘ AND password = ‘”.$_POST['password'].”‘”
Someone could login by using any username and for the password they would type ‘ OR ”=” which would be placed into your MySQL query changing it to be:
“SELECT * FROM users WHERE username = ‘anyuser’ AND password = ” OR ”=””
As you can see, MySQL injection attacks can be pretty serious depending on the information the person has access to once they are logged in. It is very important for you to secure your site against injection attacks. Luckily, PHP can aid you in preventing injection attacks.
MySQL will then return all the rows in the table and then, depending on your script’s logic, you will probably log them in because there was a match. Now, in most cases, people have magic_quotes_gpc turned on (it’s the PHP default) which will add backslashes to escape all ‘ (single-quote), ” (double quote), (backslash) and NULL characters. This is not foolproof though because there are other characters that should be escaped to be safe.
Preventing Mysql/PHP injections:-
There are php mysql functions to prevent such type of things:
into your queries. One of The function is mysql_real_escape_string().
use Like :- $value = “‘” . mysql_real_escape_string($value) . “‘”;
For integer values dont forgot to use intval() function
mysql> INSERT INTO employee VALUES (’Fred Flinstone’,'Quarry Worker’,'Rock Digger’);
mysql> INSERT INTO employee VALUES (’Wilma Flinstone’,'Finance’,'Analyst’);
mysql> INSERT into employee values (’Barney Rubble’,'Sales’,'Neighbor’);
mysql> INSERT INTO employee VALUES (’Betty Rubble’,'IT’,'Neighbor’);
Note: Data type used was CHAR. Other data types include:
* CHAR(M) : Fixed length string. Always stores M characters whether it is holding 2 or 20 characters. Where M can range 1 to 255 characters.
* VARCHAR(M) : Variable length. Stores only the string. If M is defined to be 200 but the string is 20 characters long, only 20 characters are stored. Slower than CHAR.
* INT : Ranging from -2147483648 to 2147483647 or unsigned 0 to 4294967295
* FLOAT(M,N) : FLOAT(4,2) - Four digits total of which 2 are after the decimal. i.e. 12.34 Values are rounded to fit format if they are too large.
* DATE, TEXT, BLOB, SET, ENUM
4. Add a user. Use the MySQL SQL console to enter SQL commands. The command mysql with the correct login/password will connect you to the database. The admin tables are stored in the database “mysql”.
[prompt]$ mysql -h localhost -u root -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.41
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> USE mysql;
mysql> SHOW TABLES;
+—————–+
| Tables_in_mysql |
+—————–+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+—————–+
mysql> INSERT INTO user (Host, User, Password, Select_priv) VALUES (”, ‘Dude1′, password(’supersecret’), ‘Y’);
mysql> FLUSH PRIVILEGES; - Required each time one makes a change to the GRANT table
mysql> GRANT ALL PRIVILEGES ON bedrock.* TO Dude1;
mysql> FLUSH PRIVILEGES; - Required each time one makes a change to the GRANT table
mysql> quit
Note:
* There is NO space between the -p and the password! You can omit the password and you will be prompted for it.
* The SQL flush command is equivalent to issuing the command:
[prompt]$ mysqladmin reload
5. Test the database:
mysql> SELECT * from employee;
+—————–+—————+————-+
| Name | Dept | jobTitle |
+—————–+—————+————-+
| Fred Flinstone | Quarry Worker | Rock Digger |
| Wilma Flinstone | Finance | Analyst |
| Barney Rubble | Sales | Neighbor |
| Betty Rubble | IT | Neighbor |
+—————–+—————+————-+
1 row in set (0.00 sec)
mysql> SELECT name FROM employee WHERE dept=’Sales’;
+—————+
| name |
+—————+
| Barney Rubble |
+—————+
1 row in set (0.00 sec)
6. Quit from the SQL shell:
[prompt]$ quit
7. Shutting down the database:
[prompt]$ mysqladmin -u root -ppassword shutdown - PREFERRED
OR
[prompt]$ /etc/rc.d/init.d/mysqld stop
OR
[prompt]$ service mysqld stop
First of all you should know how you can add users in mysql databases:-
Using CREATE USER and/or GRANT commands
Inserting a new record into the mysql.user table
First let’s see how to use the CREATE USER command:-
CREATE USER user [IDENTIFIED BY [PASSWORD] ‘password‘]
e.g
mysql>CREATE USER ‘myuser’@‘localhost’ IDENTIFIED BY‘mypassword’;
Once user added in databases you need to provide privileges to that user.You can use like:-
mysql>GRANTSELECT,INSERT,UPDATE,DELETE ON *.* TO ‘myuser‘@‘localhost’;
or mysql>GRANT ALL ON *.* TO ‘myuser’@‘localhost’;
Another way for creating users in mysql is just enter directoly in user table of mysql and make few entries:-
mysql> INSERT INTO userVALUES('localhost','monty',PASSWORD('some_pass'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
or
mysql> INSERT INTO user (Host,User,Password)VALUES('localhost','usename','pass');
mysql> FLUSH PRIVILEGES;
Many times it is needed to get the structure of table in PHP file or drupal module.There may be conditions that you dont have got admin user name and passwords for cpanel/phpmyadmin.In such cases you must have idea how to know the structure using a small PHP code.you can get connection info for mysql from code files or if you are using ftp it is sure that u can run a SQl query from code file.So below is the simple script for getting table info
Simple PHP Mysql
$table_name="node";
$sql=sprintf("SHOW COLUMNS FROM %s",$table_name);
$$rs=mysql_query($sql);
while($col=mysql_fetch_object($rs))
{
print_r($col);
}
Drupal And Mysql
$table_name="node";
$sql=sprintf("SHOW COLUMNS FROM %s",$table_name);
$$rs=db_query($sql);
while($col=db_fetch_object($rs))
{
print_r($col);
}
Once you have checked structure you must be able now to add alter structure of table using same code method:-
mysql_query("ALTER TABLE user_info ADD user_coupon varchar2(50)");
