Creating and inserting data into a PHP and MySQL Database

[Peter L]

A MYSQL database can be used to store tons of information and can quickly be retrieved through the use of a query. I have not debugged this code because I don't feel like creating a new database to test with. There may be errors, if you find one please tell me.

Connecting to your mysql server! I like to use variables for the host, username and password, you can however choose differently just replace the text in for the variables.

PHP Code:

<?
//Database info
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "root";
//End Database info

$con = mysql_connect('$dbhost', '$dbuser', '$dbpass');
if(!$con) //The ! means Does/Can Not. Basically if it fails it does the first {} instead of last.
{
die ("Failed to Connect to MYSQL Database"); //Stops the execution of scripts because there is no connection
} 


Now for creating a DB. Make sure you have the previous code in your file prior to this so you can create tables.

PHP Code:

if (!mysql_query("CREATE DATABASE test_db",$con))
{
die ("Failed to create DB!");
}
else
{
echo "Database created succesfully";
} 


Now for creating a table, which is what stores the actual data in rows.

PHP Code:

mysql_select_db("test_db",$con); //Replace "test_db" with your DB.
//Info for DB
$table = "CREATE TABLE Users
(
User_ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (User_ID),
Username varchar(16),
Password varchar(32),
)";

$query = mysql_query($table,$con);
if (!$query){
die ("Failed to create Table");
}
else
{
echo "Created table succesfully";
}
?>

Now you need to insert data? I have not debugged this code. I do not have the time set up a separate db to do this so beware errors could exist.

First setup your connection to the database.

PHP Code:

<?
//Database info
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "root";
//End Database info

$con = mysql_connect('$dbhost', '$dbuser', '$dbpass');
if(!$con) //The ! means Does/Can Not. Basically if it fails it does the first {} instead of last.
{
die ("Failed to Connect to MYSQL Database"); //Stops the execution of scripts because there is no connection
}
?>

Make sure you have your table and database already created. See how to do that here. I will be using the database I made which I just linked.

PHP Code:

<?

$user = "admin";
$pass = "admin";

mysql_select_db('test_db',$con);

$insert = mysql_query("INSERT INTO Users (Username, Password) //What you what to be inserted.
VALUES ($user, $pass)");

$query = mysql_query($insert);

if(!$query){
die ("Failed to insert user");
}
else
{
echo "User added successfully";
}
?>

If we wanted to get data from another page that uses a form simply replace our variables user and pass with this. Make sure you sanitize them, this example is NOT sanitized.

PHP Code:

$user = "$_POST['user']";
$pass = "$_POST['pass']"; 


Hope you find this tutorial useful.

[Peter L]

Bumping this up, did anyone find it helpful?

[AceInfinity]

Whoa, huge SQL vulnerabilities in here though, never insert the pass as plain text into the mysql query.

[Strafeness]

You should use the mysql_real_escape_string code man. You can hack this easily.

[Peter L]

Can one of you explain how this would be hacked? I understand how, but I'd like to actually know how.

[Strafeness]

"INSERT INTO Users (Username, Password) //What you what to be inserted.
VALUES (mysql_reaL_escape_string($user), (mysql_reaL_escape_string($pass))");

[★Cooldude★]

"INSERT INTO Users (Username, Password) //What you what to be inserted.
VALUES (mysql_reaL_escape_string($user), (mysql_reaL_escape_string($pass))");

Really?

PHP Code:

function sanitise($input){
return htmlentities(strip_tags(mysql_real_escape_string($input)));
} 


[AceInfinity]

Also not to forget XSS protection.

PHP Code:

$user = htmlspecialchars($username, ENT_QUOTES);
$pass = htmlspecialchars($input_pass, ENT_QUOTES); 


[Gaijin]

Also, never use something like this...

PHP Code:

$user = "$_POST['user']";
$pass = "$_POST['pass']"; 


The above is not much, but in a case of a real project you could use up your bandwidth in now time with the above coding technique...
Double quotes require the PHP parser to scan strings for variables, which requires more time and more memory, using single quotes can improve your loading time and memory usage by even 700%....

Consider this practice...

PHP Code:

$some = 'need';
$string1 = 'Only use double quote when you '.$some.' really need to...';
# instead of
$string2 = "Only use double quote when you {$some} really need to..."; 


string1 will be parsed much faster then string2...

[Haxalot]

Im quite surprised (in a worried way that is), that no one else has picked up on the first code block problem:

PHP Code:

$con = mysql_connect('$dbhost', '$dbuser', '$dbpass'); 


So as shown in the code above, you are no longer taking the variables as what they stand for. Your treating them as literals because you have used single quotes around them, meaning that variables are no longer parsed.

Also i disagree with you ★Cooldude★ because you are using two clashing functions:

Really?

PHP Code:

function sanitise($input){
return htmlentities(strip_tags(mysql_real_escape_string($input)));
} 


strip_tags do exactly what their called, they strip the opening and closing tags (including the text inside of them), from the users input. However you're using htmlspecialchars over that again. This is rather pointless because you have already stripped the tags, and now you are trying to convert them?
I would keep it to just escaping the users data into the database (with the likes of mysql_real_escape_string, or addslashes), and then upon output of data from the database, i would use the function htmlspecialchars (to prevent XSS attacks). The reason being is that you may forget to use htmlspecialchars upon user input, which would leave you vulnerable upon output of data from your database because you have trusted all of the data inside your database (dont ever do that).