I Can Never Execute SQL-Safe Queries?

Question

I am using the ODBC extension in PHP to connect to an SQL 2000 server. Here is what I am up against:

1. I can execute queries with odbc_exec() OR
2. I can execute queries with odbc_execute()

In my opinion, the differences between these two query executing methods are nearly as different as night and day:

1. odbc_exec() will execute a non SQL-safe query and return the results from the query
2. odbc_execute() is used in conjunction with odbc_prepare() to execute an SQL safe query on the database. However, odbc_execute() can only ever return a boolean, and therefore cannot be used to return the results from a SELECT statement, or to check how many rows were updated from an UPDATE or DELETE statement

Is this really the way this all works, or is there some way to escape values for use in odbc_exec() or to get the results back from odbc_execute()?

The PHP documentation doesn't seem to over any solutions for the above dilemma.

Thank you for your time.

Answer 1

The resource for the query you're running is returned by the odbc_prepare function, not odbc_execute.

These two blocks of code do the same thing:

$query=odbc_exec("SELECT * FROM table WHERE userinput=".$hopefully_escaped_user_input);
while($row=odbc_fetch_array($query) {
  //do stuff with $row
}



$query=odbc_prepare("SELECT * FROM table WHERE userinput=?");
odbc_execute($query,Array($user_input);
while($row=odbc_fetch_array($query) {
  //do stuff with $row
}

Answer 2

Prepared statements are used in conjuction with a resource that "points to" the prepared statement.

Imagine prepared statements as if they were a function/ procedure you defined in SQL and then you use the resource to "call" that function.

Example from here:

<?php
$res = odbc_prepare($db_conn, $query_string);
if(!$res) die("could not prepare statement ".$query_string);

if(odbc_execute($res, $parameters)) {
    $row = odbc_fetch_array($res);
} else {
    // handle error
}
?>