How can I delimit all input from a form

[countrydj]

I am writing a little script that has a form input to input into a mysql database.
People will type something like:
"It's a very good club"
The problem is that "It's" needs to be "It\'s" in order to input into the database, otherwise it throws a wobbly.

There are a lot of fields in the form.

I have written a bit of code to change the "'" to "\'" in one of the fields:

PHP Code:

$change="\'";
$club_details = str_replace( "'", $change, $club_details); 


My question is:
Is it possible to do this for ALL the fields with one bit of script, or would I have to do the same for EVERY field ???

Any advise will be welcome (as long as it's what I want to hear !!!)

I notice that this forum will accept It's without me delimiting it.

Thanks,

[DanInMa]

Im not a php guy, but I remembered hearing it has some built in functionality for santizing input, perhaps this will help :

http://net.tutsplus.com/tutorials/ph...h-php-filters/

[Fou-Lu]

That's due to the lack of escaping. This is referred to as SQL Injection where you damage the SQL structure by corrupting it with input.
The process is simple:

PHP Code:

if (get_magic_quotes_gpc())
{
    $_POST['input'] = stripslashes($_POST['input']);
}

$sQry = "SELECT * FROM table WHERE input = '" . mysql_real_escape_string($_POST['input']) . "'"; 


However, MySQL library is now officially deprecated, so you should be moving to either mysqli or pdo. Both support prepared statements:

PHP Code:

$con = new MySQLi('connection details here');
if ($stmt = $con->prepare("SELECT * FROM table WHERE input = ?"))
{
    $stmt->bind_param('s', $_POST['input']);
    $stmt->execute();
    // use bind_result to fetch from the query.
    $stmt->fetch();
    $stmt->close();
} 


For example.

So, first step is always to remove magic quotes. These are gone as of 5.4, but since MySQLi is available since 5.0 they still need to be accommodated for. You can walk the entire $_POST/$_GET array:

PHP Code:

if (get_magic_quotes_gpc())
{
    $_POST = array_map('stripslashes', $_POST);
} 


That will remove any \' that exists within the $_POST string data. It's not recursive though, so if you have array input of strings, than a simple recursive method or a recursive walk would suffice with a custom method.

That will clean up the input from the form itself and return it into its raw state (ie: O'Neil instead of O\'Neil). Now we can put that into the database. With the above examples, you either filter the variable through mysql_real_escape_string (using the mysql library), or you use prepared statements with Mysqli/PDO. MySQLi also has a escape sequence for it, but prepared statements are for more secure.

Hope that helps!

[countrydj]

Hi Guys...
Thank you very much for taking the time to help me.

As it happens the solution was to edit my php.ini file.
However, you pointed me in the right direction by mentioning get_magic_quotes_gpc

I have another little problem now.
I have a script (that I have just realised had the same issue) that now puts in TWO '' .
e.g.
It's in the form results in It''s in the display.
Can you throw any light on this, please ???

[Fou-Lu]

That's referred to as a directive magic_quotes_sybase.
Disabling the magic_quotes_gpc directive is an option, but its one I don't like to rely on (some sites may not allow .htaccess or ini configuration changes by individuals on shared hosting). Hence the use of the array_map. The documentation indicates that sybase does respect the addslashes/stripslashes directives, so if you have It''s and issue a stripslash with sybase enabled, than it should convert it back to It's.
Its somewhat rare to have the sybase on (perhaps its a windows machine since that's useful for some of the SQLServer escaping), but another one to disable is the magic_quotes_runtime (which I also find somewhat rare to be enabled). So ultimately to do all the above, you can simply do:

PHP Code:

// Take care of magic_quotes_gpc if its enabled (ini per-dir only, so cannot disable at runtime)
if (get_magic_quotes_gpc())
{
    $_POST = array_map('stripslashes', $_POST); // or list each individually or write a recursive function as well ($_FILES is handled *slightly* differently for example)
}  
// Stop external resource from escaping:
ini_set('magic_quotes_runtime', 0); // ini all. 


Then keep going. Sybase carries no value without either magic_quotes_gpc or magic_quotes_runtime in use.

Fortunately, all three of these directives are gone as of 5.4. The function still remains, and I hope it will until at least PHP 7, but returns false guaranteed as of 5.4. This is good though as I don't like checking for ini_get on it since the ini parser accepts 1, on and true as valid values, but boolean will not deal with the 'on' string. That only happens when set to 'on' via .htaccess, it ends up as 1 if 'on' is used in php.ini.

[countrydj]

Hi Fou-Lu ...

I wish I could say that I understood everything that you have said.
As a matter of fact, I wish I understood any of it.

However, sorting through the 'maze' I checked my php.ini file (I run my own server).
Here is parts pertaining to magic_quotes :

PHP Code:

;###############################
 magic_quotes_gpc
   Default Value: On
   Development Value: Off
   Production Value: Off
;###############################

;##################################
magic_quotes_gpc = On
;###################################

;#####################################
magic_quotes_runtime = Off       
;#####################################

;###################################
magic_quotes_sybase = Off 
;##################################### 


Would you suggest that this configuration is correct ???

It does seem to solve the problem, but I am asking because it cause some other problem that I haven't recognised - YET.

Thanks for your help and advise..

[Fou-Lu]

I'd also shut down the magic_quotes_gpc.
The idea behind these directives was to help secure against sql injection attacks. But the actual methods used by the dbms' are not aware of these directives, so escaping them would result in double escaping. They carry little other value.

[countrydj]

Hi Fou-lu..
Thanks very much for trying to help me. I really do appreciate it.

I have now closed down (at your suggestion) the magic_quotes_gpc :

PHP Code:

;###############################
; magic_quotes_gpc
;   Default Value: On
;   Development Value: Off
;   Production Value: Off
;###############################

;##################################
magic_quotes_gpc = Off
;###################################

;#####################################
magic_quotes_runtime = Off       
;#####################################

;###################################
magic_quotes_sybase = Off 
;##################################### 


Then I ran into the original problem.

So I checked back through your original advice and added this to my script:

PHP Code:

}else if($action=="add"){

//#################################################################

if (get_magic_quotes_gpc())
{
    $_REQUEST = array_map('stripslashes', $_REQUEST);
} 

//#####################################################################
    $qry = "INSERT INTO " . $vars["table directory"] . " "; 


Please note that I changed your $_POST to $_REQUEST because that is what I am using. I presume that this is correct ????

I still had the problem so I changed it to:

PHP Code:

// Take care of magic_quotes_gpc if its enabled (ini per-dir only, so cannot disable at runtime)
if (get_magic_quotes_gpc())
{
    $_POST = array_map('stripslashes', $_POST); // or list each individually or write a recursive function as well ($_FILES is handled *slightly* differently for example)
}  
// Stop external resource from escaping:
ini_set('magic_quotes_runtime', 0); // ini all. 


That still didn't work.
I haven't changed to mysqli yet so I didn't try your 3rd suggestion.

Am I missing something or doing something wrong ???

Thanks again for your help.

[Fou-Lu]

What's the actual problem you are having?
I wouldn't change to $_REQUEST. $_REQUEST is a merging together of other superglobals, and instead you should be specifying which superglobal you are retrieving from. Unless you've configured the directive, it will include cookie as an override to any form input.
Also, if you've modified the php.ini, you must restart the apache services in order for the new directives to be set.

[countrydj]

Hi Fou-Lu ...

First of all, let me thank you for staying with me in solving my problem.
Secondly, I have changed $_REQUEST to $_POST. I changed all my $_POST and $_GET to $_REQUEST some years ago when it seemed to be 'flavour of the month'.
Thirdly, I have now changed from mysql to mysqli.
All this is because of your advice. THANK YOU !!!

Now to my problem:

The problem is that "It's" needs to be "It\'s" in order to input into the database, otherwise it throws a wobbly:

error:1064 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 New Club','','','','','','','','','nc111','','#99ff99','light_brown.jpg','#ff9' at line 1

This is because I have John's in one of the fields.
You then suggested that I include some code into the script:

PHP Code:

if($action=="add"){

//#################################################################

if (get_magic_quotes_gpc())
{
    $_POST = array_map('stripslashes', $_POST);
} 

//#####################################################################
    $qry = "INSERT INTO " . $vars["table directory"] . " "; 


and I got the same error message.

I then changed it to:

PHP Code:

if($action=="add"){

//#################################################################

// Take care of magic_quotes_gpc if its enabled (ini per-dir only, so cannot disable at runtime)
if (get_magic_quotes_gpc())
{
    $_POST = array_map('stripslashes', $_POST); // or list each individually or write a recursive function as well ($_FILES is handled *slightly* differently for example)
}  
// Stop external resource from escaping:
ini_set('magic_quotes_runtime', 0); // ini all.  

//#####################################################################
    $qry = "INSERT INTO " . $vars["table directory"] . " "; 


and I still get the same error.

I then edited my php.ini file:

;##################################
magic_quotes_gpc = On
;###################################

Restarted apache, and it worked. No error and data entered into database.

I then asked you if my php.ini file was OK:

;###############################
 magic_quotes_gpc
   Default Value: On
   Development Value: Off
   Production Value: Off
;###############################

;##################################
magic_quotes_gpc = On
;###################################

;#####################################
magic_quotes_runtime = Off       
;#####################################

;###################################
magic_quotes_sybase = Off 
;#####################################

You answered me:

I'd also shut down the magic_quotes_gpc.
The idea behind these directives was to help secure against sql injection attacks. But the actual methods used by the dbms' are not aware of these directives, so escaping them would result in double escaping. They carry little other value.

Which I did and was back to my original problem.

The only way I can get my script to work is to have my php.ini file:

;##################################
magic_quotes_gpc = On
;###################################

Should this be ON of OFF ???

Many thanks for taking the time to help me.

I NEED IT !!!

[Fou-Lu]

Rightios, so the directives are alright now, you can leave them all as off. That said, it shouldn't work with the map (ie: the map's job is to strip the slashes added by magic quotes, so it shouldn't matter if magic_quotes are enabled or not), so perhaps I've made an error on the function there and it doesn't actually map. You must have missed this one way up:
$sQry = "SELECT * FROM table WHERE input = '" . mysql_real_escape_string($_POST['input']) . "'"; . The reason why we have to jump through all these hoops with the use of magic quotes is because magic quotes first escapes the ' character giving you \' (or '' if you have sybase enabled), and then issuing a mysql_real_escape_string escapes it again, giving you \\\' instead. So you end up in storage after retrieval with a \'.

Now, if you've switched to mysqli, while the mysqli will have a real_escape_string as well, its safer to use prepared statements. Whilst the escape from the magic_quotes is still required, the insertion no longer requires escaping since the data is bound after the fact.

PHP Code:

if($action=="add"){

//#################################################################

// Take care of magic_quotes_gpc if its enabled (ini per-dir only, so cannot disable at runtime)
if (get_magic_quotes_gpc())
{
    $_POST = array_map('stripslashes', $_POST); // or list each individually or write a recursive function as well ($_FILES is handled *slightly* differently for example)
}  
// Stop external resource from escaping:
ini_set('magic_quotes_runtime', 0); // ini all.  

//#####################################################################

// That's fine above.  Even on new versions of PHP that don't support them,
// it will not hurt to force set them.  magic quotes is the only painful one since it can be set
// only as low as ini perdir, (ie: nested php.ini file for cgi build or .htaccess in module build)
// but the sybase and runtime can be set in ini all.

// to bind the prepared statements:
$sQry = "INSERT INTO table (field1, field2, field3) VALUES (?, ?, ?)";
if ($stmt = $con->prepare($sQry))
{
    // lets say we pull from $_POST['field1 - field3'] to match the above.
    // I'll skip any validation of it since I don't know your rulesets, but that would have occurred above.
    // field2 will be an integer value, and field 3 will be a double.  The first will be a string.
    $stmt->bind_param('sid', $_POST['field1'], $_POST['field2'], $_POST['field3']);
    $stmt->execute();
    $stmt->close();
}