By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,776 Members | 1,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,776 IT Pros & Developers. It's quick & easy.

upload image into oracle db using ODBC

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Jul 17 '05 #2

P: n/a
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
Jul 17 '05 #3

P: n/a
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
Jul 17 '05 #4

P: n/a
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)

Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.