OSCommerce tep_db_input vs tep_db_prepare_input

Question

I am working on a project that uses OSCommerce with MySQL and I'm confused as to when I should use tep_db_input() or tep_db_prepare_input(). I'd assume I should use tep_db_input() around any strings that are being inserted/updated, but then when should the other function be used?

For example, if I were to SELECT some data from the database, and use the result to then INSERT a row into another table, do I need to prepare the input at some point? Or just use tep_db_input again?

$width = '3"'; // 3 inches
$new_height = '3\' 5"'; // 3 feet 5 inches

$result = tep_db_query(
    "SELECT height 
     FROM measurements 
     WHERE width = '".tep_db_input($width)."'"
);

while ($row = tep_db_fetch_array($result)) {
    tep_db_query(
        "INSERT INTO measurement_history (
            field, 
            old_value, 
            new_value
        ) VALUES (
            'height',
            '".tep_db_input($row['height'])."',
            '".tep_db_input($new_height)."'
        )"
    );
}

Is this correct?

Edit:: In case anyone isn't familiar with those functions, here are their definitions:

function tep_sanitize_string($string) {
    $patterns = array ('/ +/','/[<>]/');
    $replace = array (' ', '_');
    return preg_replace($patterns, $replace, trim($string));
}

function tep_db_input($string, $link = 'db_link') {
    global $$link;

    if (function_exists('mysql_real_escape_string')) {
        return mysql_real_escape_string($string, $$link);
    } elseif (function_exists('mysql_escape_string')) {
        return mysql_escape_string($string);
    }

    return addslashes($string);
}

function tep_db_prepare_input($string) {
    if (is_string($string)) {
        return trim(tep_sanitize_string(stripslashes($string)));
    } elseif (is_array($string)) {
        reset($string);
        while (list($key, $value) = each($string)) {
            $string[$key] = tep_db_prepare_input($value);
        }
        return $string;
    } else {
        return $string;
    }
}

Vespers

Answer 1

tep_db_input uses mysql_real_escape_string or mysql_escape_string and that's a recommended way to prepare your database input. (And I guess this function will use mysqli_real_escape_string() or similiar in a later release since mysql_real_escape_string will be deprecated starting with PHP 5.5.0. )

Where tep_db_input with mysql_real_escape_string just does escaping:

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, 
which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

tep_db_prepare_input does different things like trimming whitespaces and replacing brackets and unquoting(!) by calling stripslashes.

So my advice would be: always use tep_db_input. And if you use tep_db_prepare_input to get rid of whitespace etc. use tep_db_input afterwards, too.

Aguirre

Answer 2

This is a bit weird, but you use both. Doing it this way will prevent attacks from malicious users, as well as unintended problems from unusual inputs.

Use tep_db_prepare input on any input data from the HTML form. This clears up issues with HTML, magic quotes, and script injections. Do not use this on text retrieved from the database.

Then you use tep_db_input before writing it to the database. This will escape the MySQL characters to prevent SQL injection attacks and other such problems.

Here's a code sample that shows it:

$clean = tep_db_prepare_input($_POST['name']);
$query_text = tep_db_query("select * from " . TABLE_NAME . " where name='" . tep_db_input($clean) . "'");

Menander