Posted by Andy Hassall on 05/23/05 21:38

On Sun, 22 May 2005 23:24:02 +0100, ATK <cifroes@netcabo.pt> wrote:

>Thanks for your reply, i still have some questions:
>
>Andy Hassall wrote:
>
>>> $sql = "INSERT INTO fotos (id_foto, id_cat, id_m, fotos, avg,
>>>total, foto_type) VALUES (fotografias_id.nextval, 1, $id, '$date', '0',
>>>'0', '$foto_type')";
>>
>> Eep. Use placeholders/bind variables. Do not embed variables into SQL -
>> _particularly_ under Oracle.
>>
>> Asides from the security issues due to escaping (addslashes() does NOT escape
>> strings as required by Oracle), it also results in masses of "hard parsing",
>> also the maximum length of a literal string is 4000 characters so your file
>> won't work, and also you're subjecting binary data to character set conversions
>> potentially resulting in more corruption.
>
>If addslashes is not enough, what should i use?

Placeholders, and don't put values in the SQL, bind them separately.

(Oracle doesn't quote single quotes with slashes, it uses another quote. But
this is the wrong approach, anyway).

>What do you mean "Placeholders/bind variables", can you show some
>links/code examples, and let me remember that i can only use ODBC
>functions, not oracle extension functions...

In that case I can only refer you to the manual, since I don't use ODBC.

http://uk.php.net/odbc
http://uk.php.net/manual/en/function.odbc-prepare.php
http://uk.php.net/manual/en/function.odbc-execute.php

I believe (but could be wrong) that ODBC forces you to use anonymous
placeholders, i.e. "?". So your SQL would look like:

$sql = "INSERT INTO fotos (id_foto, id_cat, id_m, fotos, avg, total, foto_type)
VALUES (fotografias_id.nextval, 1, ?, ?, '0', '0', ?)";

You'd then pass in the values to bind to the placeholders in the execute call.

Constants are OK in SQL, but variables are not, and replaced by placeholders.

Note that placeholders are not quoted, nor are the values passed to execute
escaped in any way. They are passed to the database as-is and it handles
binding them to the correct places in the statement.

I also recommend ADOdb as a layer on top of the basic database calls. I
believe it can use ODBC connections to Oracle. http://adodb.sourceforge.net/

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Back to original message]