[SOLVED] inserting data into database

[ngreenwood6]

I am going to be letting some users insert some information in the database. I have been told that i need to "filter" this data. The reason is because I do not know what is going to be inserted into the database. Users are going to be able to register and make posts. Can someone help me with what I should use to "Filter" the data?

[rhodesa]

when putting info INTO your DB, just use mysql_real_escape_string() on all the data. it will make the data safe to insert.

 

when displaying the info from the DB, you will have to watch out for html/javascript injection. if you use htmlspecialchars() when displaying, you should be fine

[ngreenwood6]

So when I go to get the data from my form I usuall do this:

 

foreach($_POST as $value);

 

But now when I do that I should do this:

 

foreach($_POST as $value)
{
$value = mysql_real_escape_string($value); 
}

 

The reason I do that is so that all of the variables have there name from the form so that I dont have to do them individually. Also what does the mysql_real_escape_string actually do?

[rhodesa]

sure. all that matters is any string you have goes through that function

[ngreenwood6]

Is that the best way to get the variables from the form?

[rhodesa]

um, depends on the circumstances. usually i get the values individually, but again, there are cases where i can see using a loop:

$user= $_POST['user'];
$value=$_POST['value'];
$sql = sprintf("INSERT INTO tablename (user,value) VALUES ('%s','%s')",mysql_real_escape_string($user),mysql_real_escape_string($value));

[ngreenwood6]

Thanks for the help.

[ngreenwood6]

I just tried it and I am getting this error:

 

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\wamp\www\help.php on line 10

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\wamp\www\help.php on line 10

 

I used this code:

 

$classroom = mysql_real_escape_string($_POST['classroom']);

[rhodesa]

a mysql connection needs to exist before you can use that function

[ngreenwood6]

ok I got you. I put it before the database connection. Thanks for the help.

[ngreenwood6]

Ok it is working the when I do the variables indvidually but when I use this:

 

//set the variables
foreach($_POST as $value)
{
$value = mysql_real_escape_string($value);
}

 

I am getting this error message:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's','Pinehurst Elementary','1','test','1229094828','no')' at line 1

 

It looks to me like it is still not filtering it. Any help?

[rhodesa]

can you post all the code?

[ngreenwood6]

No problem.

 

<?php

//include the variables
include("vars.php");

//set the date
$date = time();

//connect to the server
$conn = mysql_connect($host, $db_user, $db_pass);

//select the database
mysql_select_db($db);

//set the variables
foreach($_POST as $value)
{
$value = mysql_real_escape_string($value);
}

//query to select the tech by the school id
$tech_query = "SELECT * FROM schools WHERE school = '$school'";

//get the results from the tech
$tech_results = mysql_query($tech_query) or die(mysql_error());

//get the results
$tech_row = mysql_fetch_array($tech_results);

//set the tech id variable
$tech_id = $tech_row['tech_id'];

//make the query to insert request
$query = "INSERT INTO work (name, request, school, tech_id, classroom, date, completed) VALUES ('$name','$request','$school','$tech_id','$classroom','$date','no')";

//check to make sure name is entered
if(empty($name))
{
echo "Please enter a name!";
}
elseif(empty($request))
{
echo "Please enter your request!";
}
elseif(empty($classroom))
{
echo "Please enter a classroom!";
}
else
{
$results = mysql_query($query) or die(mysql_error());
header("Location:thank_you.php");
}

?>

[rhodesa]

<?php

//include the variables
include("vars.php");

//set the date
$date = time();

//connect to the server
$conn = mysql_connect($host, $db_user, $db_pass);

//select the database
mysql_select_db($db);

//set the variables
//foreach($_POST as $value)
//{
//$value = mysql_real_escape_string($value);
//}
$name = mysql_real_escape_string($_POST['name']);
$request = mysql_real_escape_string($_POST['request']);
$school = mysql_real_escape_string($_POST['school']);
$classroom = mysql_real_escape_string($_POST['classroom']);
$date = mysql_real_escape_string($_POST['date']);

//query to select the tech by the school id
$tech_query = "SELECT * FROM schools WHERE school = '$school'";

//get the results from the tech
$tech_results = mysql_query($tech_query) or die(mysql_error());

//get the results
$tech_row = mysql_fetch_array($tech_results);

//set the tech id variable
$tech_id = $tech_row['tech_id'];

//make the query to insert request
$query = "INSERT INTO work (name, request, school, tech_id, classroom, date, completed) VALUES ('$name','$request','$school','$tech_id','$classroom','$date','no')";

//check to make sure name is entered
if(empty($name))
{
echo "Please enter a name!";
}
elseif(empty($request))
{
echo "Please enter your request!";
}
elseif(empty($classroom))
{
echo "Please enter a classroom!";
}
else
{
$results = mysql_query($query) or die(mysql_error());
header("Location:thank_you.php");
}

?>

[ngreenwood6]

Yeah I could get it to work setting the variables myself but I was trying to get it to work using the foreach loop.

[rhodesa]

for that, it would be

foreach($_POST as $key=>$value)
{
$$key = mysql_real_escape_string($value);
}

 

BUT, this is not a good idea. it allows people to inject any variable into your code

[ngreenwood6]

Ok so you are saying that it is best just to define each value individually. My only thing was trying to make less code and if there are 50 fields then there would be alot of variable setting.

[rhodesa]

the other thing you could do is:

$p = array();
foreach($_POST as $key=>$value){
  $p[$key] = mysql_real_escape_string($value);
}

//make the query to insert request
$query = "INSERT INTO work (name, request, school, tech_id, classroom, date, completed) VALUES ('{$p['name']}','{$p['request']}','{$p['school']}','{$p['tech_id']}','{$p['classroom']}','{$p['date']}','no')";

 

[ngreenwood6]

Wow thanks for all the help this has definitely been enlightening. Oh and by the way nice website. Is that done in flash because that is pretty amazing?

[rhodesa]

Wow thanks for all the help this has definitely been enlightening. Oh and by the way nice website. Is that done in flash because that is pretty amazing?

 

yeah, mostly flash. it's getting old though, i've been meaning to update it