upload image into oracle db using ODBC

[ATK]

Hi,

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).
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(fread(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')";

....
then i execute the sql, etc...

------------------

Any ideas how should i accomplish this upload task?
thanks in advance for your time,
ATK

[Andy Hassall]

On Sun, 22 May 2005 04:23:48 +0100, ATK <ci*****@netcabo.pt> wrote:

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).
It should be BLOB - LONG RAW is deprecated.
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(fread(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')";
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.
...
then i execute the sql, etc...

Any ideas how should i accomplish this upload task?

Placeholders/bind variables.

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

[ATK]

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?

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...
Thanks again

[Andy Hassall]

On Sun, 22 May 2005 23:24:02 +0100, ATK <ci*****@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 / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Mladen Gogala]

On Sun, 22 May 2005 18:03:02 +0100, Andy Hassall wrote:

then i execute the sql, etc...

Any ideas how should i accomplish this upload task?

Placeholders/bind variables.

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)