PDO or mysql_real_escape_string to sanitize Inputs

fresher_06

`if (isset($_POST['cust_id'])) {
$cust_id = mysql_real_escape_string($_POST['qr_cd']);
//for connecting to MySql DB
include 'config.php';
session_start();
if (isset($_SESSION['merchant_id'])) { // if the Merchant is logged in
$merchant_id=$_SESSION['merchant_id'];

$sql = "SELECT * FROM customer_data where customer_id = :cust_id AND merchant_id = :merchant_id";

try {
$dbh = new PDO("mysql:host=$db_host;dbname=$db_name", $db_username, $db_pass);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare($sql);
$stmt->bindParam("cust_id", $cust_id);
$stmt->bindParam("merchant_id", $merchant_id);
$stmt->execute();`
}

Alright , I know that I am doing something wrong by mixing up the old mysql type querying mechanism with the PDO , but I am still paranoid about sanitising the "cust_id" above (or even merchant_id) ..
Do you guys suggest any secure way to sanitize the inputs before preparing the PDO statements.

dylziez

You don't need to. Prepared statements can insert anything in, including DROP 'x-x-fo'oa'sf;&&1.

You can literally bindParam("key", $_GET['something']); and be safe.

MiguelQ

How about range validation before the sql query? i.e. check for valid data type (integer or uuid, etc)

jh

@dylziez said: You don't need to.

+1

ihatetonyy

@dylziez said: You don't need to. Prepared statements can insert anything in, including DROP 'x-x-fo'oa'sf;&&1.

This.

In addition, I don't think mysql_real_escape_string works without using mysql_connect or some nonsense.

fresher_06

@ihatetonyy said: In addition, I don't think mysql_real_escape_string works without using mysql_connect or some nonsense.

yeah that was there , i just removed that here for the simplicity of code ..

@dylziez said: You don't need to. Prepared statements can insert anything in, including DROP 'x-x-fo'oa'sf;&&1.

You can literally bindParam("key", $_GET['something']); and be safe.

So it means I can simply use below code , WITHOUT sanitizing it and it will be safe ?

$stmt->bindParam("cust_id", $_POST['cust_id']);
$stmt->bindParam("merchant_id", $_POST['merchant_id]');

jh

Yes

dylziez

of course you have to actually htmlentities() it or something on the way out or you get fun persistent xss!

also, $stmt->bindParam("cust_id", $_POST['cust_id'], PDO::PARAM_INT); or something if integer

libro22

Try using filter_var to validate and sanitize inputs before queries.

raindog308

It'll be safe, though it may be garbage.

Always use bind params if you can...your DBA will thank you. Every time you submit a query, the database has to parse it and generate an execution plan. If you use bind vars, that work is already done. In large enterprise workloads, I've seen drops of 10x on CPU load just by getting developers to rewrite their top SQLs (not even all SQL) with bind vars.

jcaleb

prepared no need

kro

If I see another select star sql statement, im going to chuck a massive Woet!!