How to protect an ODBC query from SQL injection

Question

What would be the best way to protect this query from sql injection? This example is just an example, I've read a few articles on internet but can't get my head around parametrised queries. Any links to useful articles will get a vote up but I think seeing this example would help me best.

$id = $_GET["id"];
$connection = odbc_connect("Driver={SQL Server};Server=SERVERNAME;Database=DATABASE-NAME;", "USERNAME", "PASSWORD");
$query = "SELECT id firstname secondname from user where id = $id";
$result = odbc_exec($connection, $query);
while ($data[] = odbc_fetch_array($result));
odbc_close($connection);

Thanks,

EDIT: I didn't make it obvious but I'm using SQL Server not mysql. This is just an example, it won't always be a number I'm searching on. It would be nice if the answer used parametrised queries as many people suggest this and it would be the same for all query's instead of different types of validation for different types of user input.

Answer 1

I think PDO objects are the best.

In a nutshell, here is how you use them.

$databaseConnection = new PDO('mysql:host='. $host .';dbname=' . $databaseName, $username, $password);

$sqlCommand = 'SELECT foo FROM bar WHERE baz=:baz_value;';
$parameters = array(
    ':baz_value'    => 'some value'
);

$preparedStatement = $databaseConnection->prepare($sqlCommand);
$preparedStatement->execute($parameters);

while($row = $preparedStatement->fetch(PDO::FETCH_ASSOC))
{
    echo $row['foo'] . '<br />';
}

The values you would enter for the SELECT criteria are replaced with parameters (like :field_value) that begin with a colon. The paramters are then assigned values in an array which are passed separately.

This is a much better way of handling SQL queries in my opinion.

The parameters are sent to the database separately from the query and protects from SQL injection.

Answer 2

To begin with, be careful with the variables you use in your queries, specially those that come from external sources such as $_GET, $_POST, $_COOKIE and $_FILES. In order to use variables inside your queries you should:

• Cast numeric data to integer or float (whichever is appropriate)
• Use appropriate escaping to escape other data

A simple example for mysql databases:

$id = $_GET["id"];     // contains: OR 1 = 1
$name = $_GET["name"]; // contains: ' OR '' ='
$query = "SELECT * FROM table WHERE id = " . intval($id) . " AND name = '" . mysql_real_escape_string($name) . "'";
// SELECT * FROM table WHERE id = 0 AND name = '\' OR \'\' =\''

For other database, the escaping practice varies. But generally you're supposed to escape the ' character with '', so:

$id = $_GET["id"];     // contains: OR 1 = 1
$name = $_GET["name"]; // contains: ' OR '' ='
$query = "SELECT * FROM table WHERE id = " . intval($id) . " AND name = '" . str_replace("'", "''", $name) . "'";
// SELECT * FROM table WHERE id = 0 AND name = ''' OR '''' ='''

Having said that, perhaps you might want to switch to PDO. It allows you to use prepared statements, the PDO driver does all the escaping.

Answer 3

Use prepared statements. First build a statement with the odbc_prepare() function, then pass the parameters to it and execute it using odbc_execute().

This is much more secure and easier than escaping the string yourself.

Lewis Bassett's advice about PDO is good, but it is possible to use prepared statements with ODBC without having to switch to PDO.

Example code, untested!

try {
  $dbh = new PDO(CONNECTION_DETAILS_GO_HERE);
  $query = 'SELECT id firstname secondname from user where id = :id';
  $stmt = $dbh->prepare($query);
  $stmt->bindParam(':id', $id, PDO::PARAM_STR);
  $result = $stmt->execute();
  $data = $stmt->fetchAll();
} catch (PDOException $e)
  echo 'Problem: ', $e->getMessage;
}

Note: $e->getMessage(); may expose things you don't want exposed so you'll probably want to do something different on that line when your code goes live. It's useful for debugging though.

Edit: Not sure if you wanted a PDO or ODBC example but it's basically the same for both.

Edit: If you're downvoting me please leave a comment and tell me why.

Answer 4

The mysql variant came with a method called mysql_real_escape_string, which was appropriate for the version of SQL being targeted. The best thing you can do is write a method to escape the Id. It's important that your escape method is appropriate for the target database. You can also do basic type checking like is_numeric for numeric inputs will reject SQL string injections immediately.

See How to escape strings in SQL Server using PHP? and follow some of the related links for explicit examples