mysql escape with $ POST

[lndlyb4]

Hi,

I'm quite inexperienced with validating data. I've created a form that updates records in my database. I am trying to figure out how to add mysql_real_escape_string to clean quotes and 's from my code which messes up my queries.

I am trying to add it to the query below. Any advice would be appreciated. Do I add it to the query or the form?

$query = "UPDATE contacts SET
contact_name = '{$_POST['contact_name']}',
contact_description = '{$_POST['contact_description']}'

WHERE contact_id = {$_POST['contact_id']}";

-------------------------------
This is a section of the form.

print '
<tr>

<th>
Contact Name:</th><td><input type="text" name="contact_name" size="40" maxsize="100" value= "' . $row['contact_name'] . '" /></td>

</tr>

<tr>

<th>Contact Description: </th><td><input type="text" name="contact_description" size="40" maxsize="100" value= "'. $row['contact_description']. '" /></td>

</tr>

[bkeep]

I use a function to validate my data. this has been edited over time and seems to work pretty well in it's current incarnation.

//Check if magic qoutes is on then stripslashes if needed
function codeClean($var)
{
$output = '';
 if (is_array($var)) {
foreach($var as $key => $val) {
$output[$key] = codeClean($val);
 }
 } else {
$var = strip_tags(trim($var));
if (function_exists("get_magic_quotes_gpc")) {
$output = mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($var) : $var);
} else {
$output = mysql_real_escape_string($var);
}
}
return $output;
}

Call it by passing any variable or array through it. ex $_GET, $_POST $_COOKIE, $array, or $variable.
$_POST = codeClean($_POST);

This also strips slashes if your server has magic_qoutes turned on which will be depreciated in future versions of php and fully remove as of v6

[lndlyb4]

bkeep,

Thank you so much for responding.

I've made my best attempt to incorporate your code and I am not sure that I've inserted it into the UPDATE query correctly. If you can give me any further guidance, I would be grateful. (As you can see, I've only added it to the text-based variables. I'll figure out what to add to the ids later - int()?)

Here's what I have so far:

<?php

//Check if magic qoutes is on then stripslashes if needed
function codeClean($var)
{
$output = '';
if (is_array($var)) {
foreach($var as $key => $val) {
$output[$key] = codeClean($val);
}
} else {
$var = strip_tags(trim($var));
if (function_exists("get_magic_quotes_gpc")) {
$output = mysql_real_escape_string(get_magic_quotes_gpc() ? stripslashes($var) : $var);
} else {
$output = mysql_real_escape_string($var);
}
}
return $output;
}

if (isset ($_POST['submit'])) {//Handle the form.

//Define the query.

$query = "UPDATE contacts SET

contact_name =" . codeClean($_POST['contact_name']) . ",
main_category_id = '{$_POST['main_category_id']}',
status_id = '{$_POST['status_id']}',
contact_description = " . codeClean($_POST['contact_description']) . ",
primary_contact_person = " . codeClean($_POST['primary_contact_person']) . ",
pcp_title = " . codeClean($_POST['pcp_title']) . ",
pcp_email = " . codeClean($_POST['pcp_email']) . ",
secondary_contact_person = " . codeClean($_POST['secondary_contact_person']) . ",
scp_title = " . codeClean($_POST['scp_title']) . ",
scp_email = " . codeClean($_POST['scp_email']) . ",
primary_decision_maker = " . codeClean($_POST['primary_decision_maker']) . ",
status_id = '{$_POST['status_id']}',
last_action = " . codeClean($_POST['last_action']) . ",
next_action = " . codeClean($_POST['next_action']) . ",
street_address = " . codeClean($_POST['street_address']) . ",
city_id = '{$_POST['city_id']}',
state_id = '{$_POST['state_id']}',
country_id = '{$_POST['country_id']}',
web_address = " . codeClean($_POST['web_address']) . "

WHERE contact_id = {$_POST['contact_id']}";

Here is the error message I am getting:

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 Landscaping Plant Farm Inc, main_category_id = '34', status_id = '4', con' at line 3. The query was UPDATE contacts SET contact_name =Alexander\'s Landscaping Plant Farm Inc, main_category_id = '34', status_id = '4', contact_description = Descriptionefwefwef, primary_contact_person = adda, pcp_title = pcp title, pcp_email = email 1, secondary_contact_person = sec cont pers, scp_title = scp title, scp_email = ijeijdrrr, primary_decision_maker = decision maker, status_id = '4', last_action = last action, next_action = next action, street_address = 3324 10th street, city_id = '1', state_id = '3', country_id = '1', web_address = www.alexfarm.com/ WHERE contact_id = 30

"'\'s Landscaping Plant Farm Inc" is part of the contact_name, so it is rejecting the first variable in my UPDATE code. Tried formatting it a few ways to no avail.

thanks

[edited by: lndlyb4 at 10:58 pm (utc) on Oct. 26, 2009]

[bkeep]

What I would do instead of using each one singularly is do something like this as the function will work recursively on any passed array which POST is an array.

$cleaned = codeClean($_POST);

then dump your variables like so
print_r($cleaned);

and if everything looks good use them like so
$cleaned["var"]

$query = "UPDATE contacts SET 
contact_name = '" . $cleaned['contact_name'] . "',
main_category_id = " . $cleaned['main_category_id'] . ",
status_id = ". $cleaned['status_id'] . ",
contact_description = '" . $cleaned['contact_description'] . "',
primary_contact_person = '" . $cleaned["primary_contact_person"] . "',
pcp_title = '" . $cleaned['pcp_title'] . "', 
pcp_email = '" . $cleaned['pcp_email'] . ",
secondary_contact_person = " . $cleaned['secondary_contact_person'] . "', 
scp_title = '" . $cleaned['scp_title'] . "',
scp_email = '" . $cleaned['scp_email'] . "', 
primary_decision_maker = '" . $cleaned['primary_decision_maker'] . "',
status_id = " . $cleaned['status_id'] . ", 
last_action = '" . $cleaned['last_action'] . "',
next_action = '" . $cleaned['next_action'] . "', 
street_address = '" . $cleaned['street_address'] . "',
city_id = " . $cleaned['city_id'] . ", 
state_id = " . $cleaned['state_id'] . ",
country_id = " . $cleaned['country_id'] . ", 
web_address = " . $cleaned['web_address'] . "' 
WHERE contact_id = " . $cleaned['contact_id'] . ""; 

As far as int checking goes there are a few different ways. What the best way is I don't know but you could always do something like this

$cleaned['main_category_id'] = (int)$cleaned['main_category_id'];

What I normally do when doing data checks for forms is explicitly accept certain data so if a field is supposed to be numeric I check explicitly for that using ctype_digit. Same goes if it is an email address I check explicitly to validate it is an email address so on and so forth.

If the field is not what was expected I throw an error and let the user know type not accepted blah blah, address not properly formatted yadda yadda.

[lndlyb4]

OK, I think I've got the query working. (I had the quotes switched. Instead of '", I had "' and the opposite on the other side. I switched them and it seems to work.) I don't get an error message and, when I enter a contact name like Alexander's, the little possessive accent(called what?) goes into the database. Should it have a \ in front of it in the database?

Also, when I go to the page that displays the contact name, the possessive accent is not there. I think I am supposed to add the stripslashes()function to the variable that calls it from the database?

On the display page, the variables are called in different ways. I get a bit confused on how to add the stripslashes() function to the different formats for the variables.

For example, here is part of the code from the print statement:

print
"<table id=\"tbl_view_details\">

<tr><th>Company Name</th><td>$contact_name</td></tr>

<tr><th>ID</th><td>" . $_GET['contact_id'] . "</td></tr>

<tr><th>Category:</th><td>" . $row['main_category_name'] ."</td></tr>

You can see that I am getting variables in 3 different ways here. How would I add the stripslashes to the 3 formats?

I am also passing variables in the url. Like this:

<tr><th>Edit:</th><td>
<a href=\"update_contact.php?contact_id=" . $row['contact_id'] ."&" . "contact_name=" . $row['contact_name'] ."&" . "main_category_name=". $row['main_category_name']

Should I be adding the validation code to them as well?

(I hope I am not being too much of a pain. I appreciate the help.)

thanks

[lndlyb4]

bkeep,

Thanks for responding.

I just read your second post after posting mine. So, if my second post seems disconnected, that's probably why. (Other than not knowing what the heck I am doing.)

I will need to spend a little time digesting what you've written. Thanks for hanging with me on this.

thanks

[dreamcatcher]

You never have to loop through arrays to filter any part of an array, use array_map [php.net].

$_POST = array_map('mysql_real_escape_string',$_POST);

dc

[mooger35]

The problem I've encountered with that dreamcatcher is that I get a warning when $_POST contains arrays.

This is what I use instead (from php.net):

function my_array_map() {
$args = func_get_args();
$arr = array_shift($args);

foreach ($args as $fn) {
$nfn = create_function('&$v, $k, $fn', '$v = $fn($v);');
array_walk_recursive($arr, $nfn, $fn);
}
return $arr;
}

$_POST = my_array_map($_POST, 'mysql_real_escape_string');

[dreamcatcher]

The problem I've encountered with that dreamcatcher is that I get a warning when $_POST contains arrays.

Yes, I agree. array_map doesn`t handle arrays recursively. I also use a function that deals with multi dimensional arrays using array_map.

dc