upload image into oracle db using ODBC

**Andy Hassall** · Jul 17 '05, 01:17 PM

Re: upload image into oracle db using ODBC

On Sun, 22 May 2005 04:23:48 +0100, ATK <cifroes@netcab o.pt> wrote:
[color=blue]
>I'm trying to upload a image file to a oracle DB and i need to only use
>ODBC functions.
>
>In db i have a LONG RAW column (if this is not correct, please tell me).[/color]

It should be BLOB - LONG RAW is deprecated.
[color=blue]
> I'm getting the error from oracle: [Oracle][ODBC][Ora]ORA-00972:
>identifier is too long...
>
>here is my php code:
>
>if (isset($_FILES['foto']['name']) & !empty($_FILES['foto']['name'])) {
> $foto = $_FILES['foto']['tmp_name'];
> $date = addslashes(frea d(fopen($foto, "r"), filesize($foto) ));
> $id = $_SESSION['id'];
> $foto_type = $_FILES['foto']['type'];
>
> $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')";[/color]

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.
[color=blue]
>...
>then i execute the sql, etc...
>
>Any ideas how should i accomplish this upload task?[/color]

Placeholders/bind variables.

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

**ATK** · Jul 17 '05, 01:17 PM

Re: upload image into oracle db using ODBC

Thanks for your reply, i still have some questions:

Andy Hassall wrote:
[color=blue][color=green]
>> $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')";[/color]
>
>
> 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.
>[/color]

If addslashes is not enough, what should i use?

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

Thanks again

**Andy Hassall** · Jul 17 '05, 01:18 PM

Re: upload image into oracle db using ODBC

On Sun, 22 May 2005 23:24:02 +0100, ATK <cifroes@netcab o.pt> wrote:
[color=blue]
>Thanks for your reply, i still have some questions:
>
>Andy Hassall wrote:
>[color=green][color=darkred]
>>> $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')";[/color]
>>
>> 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.[/color]
>
>If addslashes is not enough, what should i use?[/color]

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).
[color=blue]
>What do you mean "Placeholde rs/bind variables", can you show some
>links/code examples, and let me remember that i can only use ODBC
>functions, not oracle extension functions...[/color]

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

PHP: ODBC - Manual

http://uk.php.net/odbc

ODBC (Unified)

PHP: odbc_prepare - Manual

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

Prepares a statement for execution

PHP: odbc_execute - Manual

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

Execute a prepared statement

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.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**Mladen Gogala** · Jul 17 '05, 01:47 PM

Re: upload image into oracle db using ODBC

On Sun, 22 May 2005 18:03:02 +0100, Andy Hassall wrote:
[color=blue][color=green]
>>then i execute the sql, etc...
>>
>>Any ideas how should i accomplish this upload task?[/color]
>
> Placeholders/bind variables.[/color]

Easy package to do this with is John Lim's wonderful ADOdb. He even
shows you how to do that.

--
You can get more of what you want with a kind word and a gun than
you can with just a kind word. (Al Kapone)

upload image into oracle db using ODBC

upload image into oracle db using ODBC

Comment

Comment

Comment

Comment