Code to prevent sql injection

PHP
1

Hi everyone,

The following code is part of a form that allows people to sign up for a newsletter.


    <form action="" method="post" id="newsletter" name="newsletter">  
      <fieldset> 
        <?php if(isset($_POST['sent']) && $_POST['sent'] == "true") { ?> <p id="confirm" style="background-color: #ded5b3; color: #00274c; padding: 5px; margin-bottom: 8px;"> <strong>Thank you – your details have been processed. </strong></p><?php } else { ?>
<p>Subscribe to our newsletter.</p>
<?php } ?>
        <div> 
<input id="firstname" name="firstname" type="text" value="First name" onfocus="if (this.value == 'First name') this.value=''" onblur="if (this.value == '') this.value='First name'" />  
                    <?php
if (ValidatedField('index_965','index_965'))  {
  if ((strpos((",".ValidatedField("index_965","index_965").","), "," . "1" . ",") !== false || "1" == ""))  {
    if (!(false))  {
?>
            <span style="color: red; margin: 0; padding: 0;"> First name is required </span>
            <?php //WAFV_Conditional index.php index_965(1:)
    }
  }
}?>
        </div>

I just wondered if it was necessary to add some sort of code to the input field that would prevent a sql injection when the form is submitted:

<input id=“firstname” name=“firstname” type=“text” value=“First name” onfocus=“if (this.value == ‘First name’) this.value=‘’” onblur=“if (this.value == ‘’) this.value=‘First name’” />

If yes, can I get some advice on how to do this?

Appreciate any assistance.

2

There is no javascript code that can successfully protect from sql injection. That kid of protection should be done on the server-side.

If using PHP, you would ensure that magic quotes are removed from values, and then you would cast them to numeric values or pass strings through mysql_real_escape_string.

3

If JS is turned off, then well your site, to say the least, is screwed.

Best solution is to use prepared statements. These are SQL statements that sent to and parsed by the database server separately from any parameters.

4

http://php.net/manual/en/pdo.prepared-statements.php

The only way to be absolutely sure, every time.

5

Thanks for the replies,

I have the following code that removes magic quotes:

<?php
if (get_magic_quotes_gpc())
{
	function stripslashes_deep($value)
	{
		$value = is_array($value) ?
				array_map('stripslashes_deep', $value) :
				stripslashes($value);

		return $value;
	}

	$_POST = array_map('stripslashes_deep', $_POST);
	$_GET = array_map('stripslashes_deep', $_GET);
	$_COOKIE = array_map('stripslashes_deep', $_COOKIE);
	$_REQUEST = array_map('stripslashes_deep', $_REQUEST);
}
?>

But how would I pass strings through mysql_real_escape_string? With the following input, could you provide an example of how it’s done?

<input id=“firstname” name=“firstname” type=“text” value=“First name” onfocus=“if (this.value == ‘First name’) this.value=‘’” onblur=“if (this.value == ‘’) this.value=‘First name’” />

6

mysql_real_escape_string($_POST[‘firstname’])

7

Please also note that mysql_real_escape_string is a good friend to have, but not the best solution for cleaning user inputs.

If you want a good read : http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html

8

The best results are obtained when you disable magic quotes from php.ini, or failing that from .htaccess.

See Disabling Magic Quotes

mysql_real_escape_string should only be used where the value is used to create a mysql string. See Example 2 on the mysql_query page.

9

Thanks everyone for the information.

10

No it isnt the only way. Using mysqli with prepare statements is a perfectly usable alternative.

Both of these have the advantage over ALL other methods in that they keep the data completely separate from the SQL and so it is impossible to inject anything into the SQL through the data. Thus no PHP is required to prevent SQL injection when you use either of these methods.

11

I agree with felgall. Using PDO has its benefits (multiple database abstraction) however, if mysql is your only DB. Then mysqli prepare stateent is a suitable choice.

mysqli is probably faster then PDO if you are just dealing with mysql by itself. * Although do not quote me on this.

12

That was a mis-post on my part; I meant to post a generic page relating to PHP Prepared Statements, and didn’t realize that one was PDO specific. As I’m someone who uses MySQLi for my own projects, I definitely agree with you!