Creating parameters to stop sql injection

Question

Right now i have a site that i need to secure from basic SQL injection attacks. The site is very basic, just a form for login and a product search page. Right now i have a file where i am keeping all of my functions used on the website. Here is a couple examples of what they look like:

function createUser($userName,$userPass){
$query = <<<STR
INSERT INTO Users (userName,userPass,userTypeID)
VALUES ('$userName','$userPass',2)
STR;
return executeQuery($query);
}

Or

function getProductByName($productName){
$query = <<<STR
SELECT productID,productName, productPic,productDesc 
FROM Products
WHERE productName LIKE '%$productName%'
STR;


return executeQuery($query);
}

I want to change these so that they make use of prepared statements but am having trouble understanding how i can convert them. I found examples that make use of bindParam such as this one:

$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City) 
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd)
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();

Can i incorporate bindparam into my functions somehow? Any leads would be great.

kero · Accepted Answer · 2015-12-06 21:19:34Z

1

To convert them, just do the prepare() and bindParam() parts in your shown functions and the execute() in the executeQuery() function like this

function createUser($userName,$userPass,$dbh) {
    $stmt = $dbh->prepare("INSERT INTO Users (userName, userPass, userTypeID) VALUES (:name, :pass, :id)");
    $stmt->bindParam(':name', $userName);
    //etc
    return executeQuery($stmt);
}

function executeQuery($stmt) {
    $stmt->execute();
}

If you don't want to inject the $dbh, you can use global $dbh; or similar methods in the function (and don't have to change your existing code base).

But of course this will be very repetitive, as you need to write multiple bindParam() statements. The simpler way, assuming you are using PDO, would be to pass an array to execute() (as shown in the manual)

function createUser($userName,$userPass,$dbh) {
    $stmt = $dbh->prepare("INSERT INTO Users (userName, userPass, userTypeID) VALUES (:name, :pass, :id)");
    $params = array(
        ':name' => $userName,
        //etc
    );
    return executeQuery($stmt, $params);
}

function executeQuery($stmt, $params) {
    $stmt->execute($params);
}

To elaborate a bit: The real power of bindParam() comes when you a) want to use the $data_type or $length parameters (What are those?) or b) when you are using multiple execute() statements, eg a bulk insert

$stmt = $dbh->prepare("INSERT INTO users (name) VALUES (:name)");
$stmt->bindParam(":name", $name);
for (...) {
    $name = ...
    $stmt->execute();
}

edited Dec 6, 2015 at 21:19

answered Dec 6, 2015 at 21:13

kero

10.7k5 gold badges44 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SheepCity Over a year ago

Thanks for the info. This may be a stupid question but one thing i am confused about is this line here: $stmt = $dbh->prepare("INSERT INTO users (name) VALUES (:name)"); what is $dbh doing? Should this be declared somewhere else? Is this the database connection? I see some people online declaring is as <?php $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass); ?> is this right?

kero Over a year ago

@SheepCity It is the "db handler", so it holds information about the connection, correct. And the code you posted looks right, for more information please read the official manual. Why is this necessary? You may have multiple databases and so you can differentiate between them. Previously PHP assumed, now you need to tell it explicitly

Collectives™ on Stack Overflow

Creating parameters to stop sql injection

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related