Smalltalk › Frameworks & Tools › DBXTalk

Bind variables in statements & procedures

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

4 messages Options

recursive

Bind variables in statements & procedures

Hi,

I've been looking at the DBTalk tests/examples and also at OpenDBX,
and it looks like binds variables aren't supported in SQL statements
and more importantly for myself when using them in procedures.

In Java's JDBC:

String sqlStmt = "SELECT * FROM employees where last_name=? and
first_name like ?";
prepStmt = conn.prepareStatement(sqlStmt);
prepStmt.setString(1, bindvarLastName);
prepStmt.setString(2, bindvarFirstName);
rs = prepStmt.executeQuery();

In Perl DBI you can do this when using Oracle PL/SQL, which allows you
to get return values from the procedure using bind variables in this
case $message and $status, when call a PL/SQL procedure:

# bind variable
$message = undef;
$status = undef;

# prepare query
my $sth = $dbh->prepare(
"BEGIN
test.testProc(:status, $username,:message);
END;"
);
$sth->bind_param_inout(":status", \$status, 10);
$sth->bind_param_inout(":l_message", \$message, 500);

# execute query
$sth->execute;

It looks like this is a limitation of OpenDBX rather than DBXTalk, but
if it is the case it's a big omission when you compare it against
other languages database interface APIs, such as Java's JDBC, Perl's
DBI and Python cx_Oracle for example. This is also not so good from a
security perspective as bind variables are one of the recommended
defences against SQL injection attacks.

Am i correct in thinking this, or I have I missed something ?

Thanks

Mariano Martinez Peck

Re: Bind variables in statements & procedures

On Fri, Apr 6, 2012 at 10:43 PM, kromakey <[hidden email]> wrote:

Hi,

I've been looking at the DBTalk tests/examples and also at OpenDBX,
and it looks like binds variables aren't supported in SQL statements
and more importantly for myself when using them in procedures.

In Java's JDBC:

String sqlStmt = "SELECT * FROM employees where last_name=? and
first_name like ?";
prepStmt = conn.prepareStatement(sqlStmt);
prepStmt.setString(1, bindvarLastName);
prepStmt.setString(2, bindvarFirstName);
rs = prepStmt.executeQuery();

In Perl DBI you can do this when using Oracle PL/SQL, which allows you
to get return values from the procedure using bind variables in this
case $message and $status, when call a PL/SQL procedure:

# bind variable
$message = undef;
$status = undef;

# prepare query
my $sth = $dbh->prepare(
"BEGIN
test.testProc(:status, $username,:message);
END;"
);
$sth->bind_param_inout(":status", \$status, 10);
$sth->bind_param_inout(":l_message", \$message, 500);

# execute query
$sth->execute;

It looks like this is a limitation of OpenDBX rather than DBXTalk,

Unfortunatly, yes, I think it is a limitation of OpenDBX.
At least it was planned: "Prepared statement interface" in http://www.linuxnetworks.de/doc/index.php/OpenDBX/Future

but
if it is the case it's a big omission when you compare it against
other languages database interface APIs, such as Java's JDBC, Perl's
DBI and Python cx_Oracle for example. This is also not so good from a
security perspective as bind variables are one of the recommended
defences against SQL injection attacks.

For that we have: http://www.linuxnetworks.de/doc/index.php/OpenDBX/C_API/odbx_escape

Am i correct in thinking this, or I have I missed something ?

Sorry for the bad news, but I think you are correct.

--
Mariano
http://marianopeck.wordpress.com

recursive

Re: Bind variables in statements & procedures

Thanks for the confirmation :(

It's a pity Oracle won't/haven't released the source for their pure
Java JDBC Thin Client as I think someone could use that as a basis for
writing a pure Smalltalk Oracle driver.

Would it be feasible to write a Pharo plugin for the Oracle client
library ? I haven't much idea how to write a plugin, is there any
decent documentation on how begin ? Alternatively, is there any other
way you could interact/interface to the Oracle client library, FFI may
be or is that just meant to be used for isolated function calls ?

Thanks

On Apr 7, 3:23 pm, Mariano Martinez Peck <[hidden email]>
wrote:

> On Fri, Apr 6, 2012 at 10:43 PM, kromakey <[hidden email]> wrote:
> > Hi,
>
> > I've been looking at the DBTalk tests/examples and also at OpenDBX,
> > and it looks like binds variables aren't supported in SQL statements
> > and more importantly for myself when using them in procedures.
>
> > In Java's JDBC:
>
> > String sqlStmt = "SELECT * FROM employees where
> > last_name=? and
> > first_name like ?";
> > prepStmt = conn.prepareStatement(sqlStmt);
> > prepStmt.setString(1, bindvarLastName);
> > prepStmt.setString(2, bindvarFirstName);
> > rs = prepStmt.executeQuery();
>
> > In Perl DBI you can do this when using Oracle PL/SQL, which allows you
> > to get return values from the procedure using bind variables in this
> > case $message and $status, when call a PL/SQL procedure:
>
> > # bind variable
> > $message = undef;
> > $status = undef;
>
> > # prepare query
> > my $sth = $dbh->prepare(
> > "BEGIN
> > test.testProc(:status, $username,:message);
> > END;"
> > );
> > $sth->bind_param_inout(":status", \$status, 10);
> > $sth->bind_param_inout(":l_message", \$message, 500);
>
> > # execute query
> > $sth->execute;
>
> > It looks like this is a limitation of OpenDBX rather than DBXTalk,
>
> Unfortunatly, yes, I think it is a limitation of OpenDBX.
> At least it was planned: "Prepared statement interface" inhttp://www.linuxnetworks.de/doc/index.php/OpenDBX/Future
>
> > but
> > if it is the case it's a big omission when you compare it against
> > other languages database interface APIs, such as Java's JDBC, Perl's
> > DBI and Python cx_Oracle for example. This is also not so good from a
> > security perspective as bind variables are one of the recommended
> > defences against SQL injection attacks.
>
> For that we have:http://www.linuxnetworks.de/doc/index.php/OpenDBX/C_API/odbx_escape
>
>
>
> > Am i correct in thinking this, or I have I missed something ?
>
> Sorry for the bad news, but I think you are correct.
>
> --
> Marianohttp://marianopeck.wordpress.com

Guillermo Polito

Re: Bind variables in statements & procedures

On Sat, Apr 7, 2012 at 9:37 PM, kromakey <[hidden email]> wrote:

Thanks for the confirmation :(

It's a pity Oracle won't/haven't released the source for their pure
Java JDBC Thin Client as I think someone could use that as a basis for
writing a pure Smalltalk Oracle driver.

He, if Microsoft SQLServer and Oracle release their protocols, then it could be easy to implement full smalltalk libraries (which has lot's of advantages)...

Would it be feasible to write a Pharo plugin for the Oracle client
library ? I haven't much idea how to write a plugin, is there any
decent documentation on how begin ? Alternatively, is there any other
way you could interact/interface to the Oracle client library, FFI may
be or is that just meant to be used for isolated function calls ?

With FFI you can interact with external libraries (that's actually what dbxtalk does with opendbx) and you can build object oriented libraries on top of it.
Look at the OpenDBXDriver package of the dbxtalk project to figure out how should it be to interact with a library...

BTW, the main reason of dbxtalk interacting with opendbx instead of Oracle directly is the ammount of work that should lead to mantain bindings to:
-oracle
-sqlserver
-pg
-mysql
-etc...

The way it's done right now, we have only to mantain opendbx bindings.

Then, there already exists full smalltalk libraries for PG and Mysql (you can find them in squeaksource), the problem again, are the propietary vendors.

Guille

Thanks

On Apr 7, 3:23 pm, Mariano Martinez Peck <[hidden email]>
wrote:

> On Fri, Apr 6, 2012 at 10:43 PM, kromakey <[hidden email]> wrote:
> > Hi,
>
> > I've been looking at the DBTalk tests/examples and also at OpenDBX,
> > and it looks like binds variables aren't supported in SQL statements
> > and more importantly for myself when using them in procedures.
>
> > In Java's JDBC:
>
> > String sqlStmt = "SELECT * FROM employees where
> > last_name=? and
> > first_name like ?";
> > prepStmt = conn.prepareStatement(sqlStmt);
> > prepStmt.setString(1, bindvarLastName);
> > prepStmt.setString(2, bindvarFirstName);
> > rs = prepStmt.executeQuery();
>
> > In Perl DBI you can do this when using Oracle PL/SQL, which allows you
> > to get return values from the procedure using bind variables in this
> > case $message and $status, when call a PL/SQL procedure:
>
> > # bind variable
> > $message = undef;
> > $status = undef;
>
> > # prepare query
> > my $sth = $dbh->prepare(
> > "BEGIN
> > test.testProc(:status, $username,:message);
> > END;"
> > );
> > $sth->bind_param_inout(":status", \$status, 10);
> > $sth->bind_param_inout(":l_message", \$message, 500);
>
> > # execute query
> > $sth->execute;
>
> > It looks like this is a limitation of OpenDBX rather than DBXTalk,
>
> Unfortunatly, yes, I think it is a limitation of OpenDBX.

> At least it was planned: "Prepared statement interface" inhttp://www.linuxnetworks.de/doc/index.php/OpenDBX/Future

>
> > but
> > if it is the case it's a big omission when you compare it against
> > other languages database interface APIs, such as Java's JDBC, Perl's
> > DBI and Python cx_Oracle for example. This is also not so good from a
> > security perspective as bind variables are one of the recommended
> > defences against SQL injection attacks.
>
> For that we have:http://www.linuxnetworks.de/doc/index.php/OpenDBX/C_API/odbx_escape
>
>
>
> > Am i correct in thinking this, or I have I missed something ?
>
> Sorry for the bad news, but I think you are correct.
>
> --
> Marianohttp://marianopeck.wordpress.com