CODEWITHSUNDEEP
phppdo
Alegro
Alegro

Reputation: 7956

Do I need prepared statement on each query?

$query = "SELECT 1 FROM users WHERE username = :username";
$query_params = array(':username' => $_POST['username']);
try
{
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch(PDOException $ex)
{
die("Failed to run query: " . $ex->getMessage());
}

$row = $stmt->fetch();
if($row)
{
die("This username is already in use");
}

This all works, but:

  1. Do I really need prepared statement if the query is just SELECT or SELECT COUNT ?
    Because, if there is no INSERT / UPDATE / DELETE operations on the table - I suppose there is no dangerous of sql injection or spam ?

  2. Do I really need try/catch statement each time I go to database ?

Upvotes: 0

Views: 550

Answers (4)

davidethell
davidethell

Reputation: 12018

There is always a danger of SQL injection even on SELECT statements because someone could terminate the SELECT and append an INSERT statement in the username. However, if you are using mysql_real_escape_string() or else your DB classes escape your values for you then you don't have to worry about try/catch on a SELECT statement. If you have escaped your values this is sufficient for your SQL:

$username = mysql_real_escape_string($username); // escape the string first.
$query = "SELECT 1 FROM users WHERE username = '$username'";

Upvotes: 2

samayo
samayo

Reputation: 16495

As far as connection to database goes this is the only approach you need. Try and Catch: (if you are using MySql database )

try {
    $conn = new PDO('mysql:host=localhost;dbname=DBNAME', 'USER', 'PASS', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Plus, there is a built-in count query for count:

$affected_rows = $stmt->rowCount();

Here is a good tutorial, if you never knew

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

Upvotes: 1

asmecher
asmecher

Reputation: 1055

1) No, you don't have to use prepared statements; you could use e.g. PDO::query and PDO::quote to build up a query using string concatenation. HOWEVER -- YES, any time you're using externally-supplied strings, there is a risk of damage from SQL injection, even if you're just doing a SELECT. For example, an attacker could try to run two statements in one by using a ";" in the supplied string. The PDO::quote is another way to safeguard against this.

2) You could throw the error out of your calling code, but somewhere you'll have to consider error handling.

Upvotes: 2

Oskar Persson
Oskar Persson

Reputation: 6743

If there are any variables that you put in the query that the user can alter in any way, you should(must) use prepared statements.

Upvotes: 2

Related Questions