Saving values in UPPERCASE in a database

Thread view:

Rune Runnestø - 02 May 2005 13:01 GMT

Hi,

When connecting to a database from a JSP-file, I write for instance:
sql = "insert into person values(" + newNr + ", '" + forName + "', '" +
lastName + "')";

Statement stmt = null;
stmt.executeUpdate(sql);

The question is: How do I write the sql-sentence if I want to save the
variables in the database in UPPERCASE ?

Regards
Rune

Reply to this Message

Malte - 02 May 2005 13:44 GMT

> Hi,
>
[quoted text clipped - 10 lines]
> Regards
> Rune

In a small test window I did this:

create table strtest (test varchar2(64));

insert into strtest values (upper('test'));
insert into strtest values ('test');

commit;
select * from strtest;

Result:

TEST
test

drop table strtest;

You could also, of course, uppercase the String objects BEFORE you pass
them to the database.

Reply to this Message

Malte - 02 May 2005 13:44 GMT

>> Hi,
>>
[quoted text clipped - 10 lines]
>> Regards
>> Rune

BTW, connection to the database from the JSP could be constructed as a
poor design. I believe that many people would stick their database code
into a bean of sorts.

Reply to this Message

Chris Uppal - 02 May 2005 14:12 GMT

> sql = "insert into person values(" + newNr + ", '" + forName + "', '" +
> lastName + "')";

Unless "forName" and "lastName" come from a guaranteed safe source (i.e. /NOT/
a user typing it in, and definitely not anything on the Web), then this opens
up a potentially very serious security hole. If you don't understand what I'm
talking about then Google for "SQL injection attack".

-- chris

Reply to this Message

Thomas Kellerer - 02 May 2005 14:56 GMT

> Hi,
>
[quoted text clipped - 10 lines]
> Regards
> Rune

What's wrong with:

sql = "insert into person values(" + newNr + ", '" + forName.toUpperCase() + "',
'" + lastName.toUpperCase() + "')";

Thomas

Reply to this Message

Rune Runnestø - 02 May 2005 16:17 GMT

> What's wrong with:
>
> sql = "insert into person values(" + newNr + ", '" + forName.toUpperCase() + "',
> '" + lastName.toUpperCase() + "')";

This code works. Thanks.
Rune

Reply to this Message

shakah - 02 May 2005 17:04 GMT

You're probably better off using a PreparedStatement and the database's
concept of upper case. It handles NULLs and allows you to avoid
worrying about single-quotes in your data (e.g. last names like
"O'Brien"):

// ...guessing on the first value's type (int?)
java.sql.PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO person VALUES(?,?,?)"
) ;
int nFld=0 ;
pstmt.setInt(++nFld, new Integer(newNr)) ;
pstmt.setString(++nFld, forName) ;
pstmt.setString(++nFld, lastName) ;
pstmt.executeUpdate() ;

> > What's wrong with:
> >
[quoted text clipped - 4 lines]
> This code works. Thanks.
> Rune

Reply to this Message

Saving values in UPPERCASE in a database

Free Magazines