MySql Insert Statement

[arunpatal]

Hi,

 

I use mysql_escape_string to insert data into my database......

 

example: $name  = mysql_escape_string($_POST["name"]); $lname  = mysql_escape_string($_POST["lname"]);

 

mysql_query(INSERT INTO table (fname,lname) VALUES ('$fname','$lname'));

 

But i am not sure if this is safe enough to protect from mysql injuction....

 

My question is if it's safe to insert data like this?????

Edited April 11, 2014 by arunpatal

[Jacques1]

Hi,

 

all mysql_* functions are obsolete since more than 10 years and will be removed in the near future. The PHP manual has a big red warning on every page and a detailed explanation of the two “new” extensions (PDO and MySQLi).

 

Is your code safe from SQL injections? Well, it depends. Manually escaping the input is very fragile, because the function may not recognize the critical characters due to encoding issues. For example, it's well known that using a SET NAMES query together with an exotic encoding like GBK can break the escaping mechanism entirely.

 

A much more secure solution is to use prepared statements. Both PDO and MySQLi support them, but not the old extension.

Edited April 11, 2014 by Jacques1

[arunpatal]

Hi,

 

i was testing this example

<?php

$con = mysqli_connect("localhost","test","","test");

if(isset($_POST["name"])):

	$stmt = $con->prepare("INSERT INTO products (name,price) VALUES (?,?)");
	$stmt->bind_param('si', $name, $price);
	
	$name = $_POST["name"];
	$price = $_POST["price"];

	$stmt->execute();
	
	echo "Inserted {$name},{$price} into database\n";;

endif;

?>

<form method="post">
<input type="text" name="name" />
<input type="number" name="price"  />
<input type="submit" />
</form>

But when i write

 

<script>alert("hack")</script>

 

into name input field then the code execute..........

 

What am i doing wrong???

[jazzman1]

<script>alert("hack")</script> is a test for reflected cross site scripting (XSS) not for SQL injections

[arunpatal]

<script>alert("hack")</script> is a test for reflected cross site scripting (XSS) not for SQL injections

 

So should i use htmlspecialchars function to skip script tags??

[jazzman1]

Yes.

[Jacques1]

But do not apply this function when you insert the input into the database. The last thing you want is a database full of messed up strings. Store the original input and escape it when needed.

 

Also be aware that my comment above applies here as well: Manual escaping is very fragile. Make sure to explicitly set the character encoding of the HTML document (preferrably through the Content-Type header). And then set the exact same encoding for the htmlspecialchars() function.

 

For example:

<?php

header('Content-Type: text/html;charset=utf8');

?>
<h1>XSS Test</h1>
<?= htmlspecialchars('<script>alert("XSS")</script>', ENT_QUOTES, 'UTF-8') ?>

Without this, there's no guarantee that the function does anything whatsoever.

 

If you want to do more than the bare minimum, you should also use Content Security Policy to block all inline scripts. This serves as a second layer of defense in case you fail to properly escape the values. The concept is simple: If the client's browser supports this feature, it will only accept external JavaScript files from the domains you've marked as trusted. All other code is blocked. So even if an attacker manages to inject JavaScript code into your page, they can't get it to execute.

<?php

header('Content-Type: text/html;charset=utf8');

// block all JavaScript code and CSS declarations unless they're served from https://yoursite.com
header("Content-Security-Policy: default-src 'none'; script-src https://yoursite.com; style-src https://yoursite.com");

?>
<h1>XSS Test</h1>
<!-- This will be blocked in all modern browsers -->
<script>
	alert('XSS');
</script>