Validate form for sql input 2

[georgeocrawford]

Hi,

How can I validate the contents of my html form using php to remove all the characters which will conflict with MySql?

(e.g. I tried to submit the word you're from a form, which created an error because of the apostrophe.)

I don't actually know which characters are not allowed in MySql, so I could do with a list of them!

My sql field types are all either blob, varchar or date.

Thanks

 

[Bastien]

first of all, very bad idea to store straight html in DB, better to convert the html tags to something less dangerous

could use str_replace to replace all the '<' and '>' with '[' and ']' and use the addslashes function to escape any apostrophes in the data...

this would take

$test=&quot;<b>Hi there, you're ugly</b>&quot;

to $test=&quot;Hi there, you\'re ugly&quot;

just remember to undo these changes when you want to display the data Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[georgeocrawford]

Sorry, I didn't explain myself properly!

I am using php to enter the form values to the MySql database. This is working perfectly - html is NOT involved!

The only problem is when a user enters an illegal character (such as ' ) in a field.

Can I run a php script to either remove all the illegal characters, or ask the user to change them.

George

 

[sleipnir214]

PHP has a function to do that for you. Take a look at mysql_escape_string () (

http://www.php.net/manual/en/function.mysql-escape-string.php)

______________________________________________________________________
TANSTAAFL!

 

[georgeocrawford]

Thanks - that was the function I was looking for!

Next problem:

When the user has completed the form, they are taken to a page where their data is displayed in an html table for them to check.

If their input includes the &quot; character, the table structure will be disrupted.

I need a php script to replace all the &quot; symbols which appear in my form variables with the escaped alternative*, so the &quot; will display properly in html.

Thanks

*NB - I know what the escape character is - it's

&_quot;

without the

_

, but I can't get it to display with TGML without it being read as html!

 

[georgeocrawford]

OK, I think I've found it.

Am I right to use:

str_replace("\"", "& quot;", $myvariable);

Reply only if I am wrong!

 

[sleipnir214]

I am assuming the space between ampersand and &quot;quot;&quot; in your post is solely for the purposes of getting a browser to show the string. ______________________________________________________________________
TANSTAAFL!

 

[georgeocrawford]

Correct!

Can you help me just one stage further?

I am not very confident with arrays yet. I guess, however, that I could perform this function:

$escapedvariable = str_replace("\"", "& quot;", $variable);

on a long list of variables using an array and mabye the

foreach

function, rather than have to do them all seperately like this:

$escapedvariable = str_replace("\"", "& quot;", $variable);

$escapedvariable2 = str_replace("\"", "& quot;", $variable2);

$escapedvariable3 = str_replace("\"", "& quot;", $variable3);

Also, can I replace all the < and > symbols with

& lt;

and

& gt;

respectively, using the same array?

i.e. something like:

array = variable, variable2, variable3.......
foreach array value
{
str_replace("\"", "& quot;", $array);
str_replace(&quot;<&quot;, &quot;& lt;&quot;, $array);
str_replace(&quot;>&quot;, &quot;& gt;&quot;, $array);
}

in simplified terms.

Thanks again

 

[sleipnir214]

You any good with regular expressions?

PHP's preg_* family of functions (

http://www.php.net/manual/en/ref.pcre.php)

has a function preg_replace () (

http://www.php.net/manual/en/function.preg-replace.php)

which can take arrays in the search and replace places in the input parameters. Loop that function over the elements of your array.

Something like:

$find = array ('/&quot;/', '/</', '/>/');

$replace = array ('& quot;', '& lt;', '& gt;');

foreach ($line_array as $key => $line)
{
   $line_array[$key] = preg_replace ($find, $replace, $line_array[$key]);
}

______________________________________________________________________
TANSTAAFL!

 

[georgeocrawford]

I so nearly understand this!

Can you just show me the code again with a line to insert my array of variables to be treated by this function, i.e.:

$array = array ($function1, $function2, $function3);

And, to fit in with your code, should this array be called $line_array, or $line, or something else?

 

[sleipnir214]

Do you mean...

$array = array ($function1, $function2, $function3);

$find = array ('/&quot;/', '/</', '/>/');

$replace = array ('& quot;', '& lt;', '& gt;');

foreach ($array as $key => $line)
{
   $array[$key] = preg_replace ($find, $replace, $array[$key]);
}

______________________________________________________________________
TANSTAAFL!

 

[danielhozac]

Or let preg_replace do all the work:

$array = array($variable1, $variable2, $variable3);
$find = array('/&quot;/', '/</', '/>/');
$replace = array('& quot;', '& lt;', '& gt;');
preg_replace($find, $replace, $array);

//Daniel

 

[sleipnir214]

Oops, missed that one.

I remembered that &quot;find&quot; and &quot;replace&quot; can be arrays. I'd forgotten that the &quot;haystack&quot; can be an array too.

______________________________________________________________________
TANSTAAFL!

 

[georgeocrawford]

I have tried both sleipnir214 and Daniel's suggestions, without success.

Is this right as a test script:

$a = &quot;a <test> variable&quot;;
$b = &quot;another\&quot;test\&quot;variable&quot;;

$array = array($a, $b);
$find = array('/&quot;/', '/</', '/>/');
$replace = array('& quot;', '& lt;', '& gt;');
preg_replace($find, $replace, $array); 

echo $a;
echo $b;

Cos it just returns

a <test> variableanother&quot;test&quot;variable

in the html code, which prints as:

a variableanother&quot;test&quot;variable

What am I doing wrong?

 

[danielhozac]

A better test script would be:

<?php
$a = &quot;a <test> variable&quot;;
$b = &quot;another\&quot;test\&quot;variable&quot;;

$array = array($a, $b);
$find = array('/&quot;/', '/</', '/>/');
$replace = array('& quot;', '& lt;', '& gt;');
$array = preg_replace($find, $replace, $array);

echo $array[0];
echo $array[1];
?>

Since PHP functions regularly don't pass it's arguments as references, and array's are the values, not the addresses, you have to echo the array's contents, as the $a and $b variables only has been copied to the $array variable. //Daniel