How do you insert data from a form to MySQL securely using PHP and display it back on another page?

Answer 1

First create the form:

Now that we created the form it'll take it to frontPage.php. The "name" in the input tag is what the name of the $_POST is. So the username input would be $_POST['username']. It's POST because that is the method we used. We could also use GET(which would change to $_GET instead of $_POST)which shows the names of the inputs and what their value is. So say our username we typed in was foo$_POST['username'] would equal foo.

$username = $_POST['username'];

$password = $_POST['password'];

$username = mysql_escape_string($username);

$password = mysql_escape_string($password);

//Connect

$link = mysql_connect('mysql_host','mysql_username',mysql_password)

OR die(mysql_error());

//Connect to DB

$db = mysql_select_db('mysql_database',$link);

//Query To Insert Into Database

$mysql = "INSERT INTO db_table(username,password)

VALUES('$username','$password);

if(!mysql_query($mysql))

{

die(mysql_error());

}

?>

So now for what is going on. The $_POST['username'] and $_POST['password'] is the data from the form earlier. The $username = $_POST['username'] and $password = $_POST['password'] just makes it easier to type. Here comes the security. The mysql_real_escape_string puts a "" in front of any special characters that are in the string. symbols like <>'"%$ etc. gets a "" in front of it just in case someone is trying to inject Javascript or something into the input fields. It basically closes out symbols so nothing can be injected into the input fields.

Example:

Say we have an login form and we have $_POST['username'] and $_POST['password'] from the login and we do this code.

$username = $_POST['username'];

$password = $_POST['password'];

$mysql = 'SELECT * FROM db_table WHERE username = "$username" AND password = "$password"';

mysql_query($mysql);

if someone would put ' OR ''=' in the password field and foo in the username field the SELECT statement would become SELECT * FROM db_table WHERE username = 'foo' and password = 'OR ''=''. This is very very bad. This confuses mysql and the password comes back true and lets the person login without a valid password. So if there a username of foo the person would be able to login to their account. With mysql_real_escape_string it puts the "" in so it escapes that OR function so it would show up as ' OR ''=' and not return TRUE.

Now for the Query to the database. $mysql is the statment that inserts something into the table. So it's INSERT INTO db_table(whatever table you want to put it in)(username,password(what your categories are in your table)VALUES('$username',$password'(whatever your $_POST are for those categories))

The mysql_query($mysql) in the if statement is to put the data into the table. The if statment is very good to use when you're developing a website because what it does is it means if mysql_query($mysql) doesn't go into the table kill the rest of the script and display the error.

Now to get the data FROM the table, here is how you do it.

//Connect

$link = mysql_connect('mysql_host','mysql_username',mysql_password)

OR die(mysql_error());

//Connect to DB

$db = mysql_select_db('mysql_database',$link);

//The SELECT STATEMENT

$mysql = "SELECT * FROM db_table WHERE username = 'foo'";

//QUERY THE SELECT STATMENT

$query = mysql_query($mysql);

while($row = mysql_fetch_array($query,MYSQL_ASSOC))

{

echo $row['username'];

echo $row['password'];

}

I wont go over the SELECT and QUERY statement. The while loop is basically while this equals that do something. For this one it means while $row = mysql_fetch_array($query,MYSQL_ASSOC) do something.

The mysql_fetch_array returns the different rows from the table. So it fetches the $query and the MYSQL_ASSOC is so you can use the row names instead the number.

So the $row becomes a string with the mysql_fetch_arrayin it so it becomes $row['row name'].