How to insert a string that contains ' or ,

Thread view:

macit - 05 Feb 2008 21:32 GMT

Hello,

i am using the following statement to insert a message from a user
into the database.

updStmt.executeUpdate("INSERT INTO Contact (uId, replyAddr, subject,
msg) "+
"VALUES (" + userId + "," +
"\'" + replyAddr + "\'," +
"\'" + subject + "\'," +
"\'" + msg + "\')");

The variables userId, replyAddr, subject und msg are of type String.
Now if one of the Strings contain a ' or a , character, the sql
statement gets messed up and causes a SqlException. Is there a way
(How) can i store a string that contains ' or , in the database?
Thanks in advance for your assistance.

Reply to this Message

Donkey Hot - 05 Feb 2008 21:28 GMT

macit <ilona.ersek@gmail.com> wrote in news:3c0b6853-4092-4690-aefe-
b9d4eaabffe8@q21g2000hsa.googlegroups.com:

> Hello,
>
[quoted text clipped - 13 lines]
> (How) can i store a string that contains ' or , in the database?
> Thanks in advance for your assistance.

Yes. First google "sql injection" and learn that bad guys can own you and
your system with the code you provided.

You can use PreparedStatement like

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)

You create statemets with placeholders (?), and bind variables ot values to
those placeholders. That way the variables CAN hold ` or , characters, and
they do not spoil the SQL statement.

If I ever see a SQL implementation what you showed, I will sack the
programmer.. Well maybe not, but I will lower his salary, and try to tell
him about "sql injection".

Never, never, EVER, do not ever build SQL statements dynamically with
variables like that.

ALWAYS use ? placeholders. That is a pivilege to you as a java-programmer.
Leave the SQL-injection to those pesky php-guys.

Reply to this Message

macit - 05 Feb 2008 21:56 GMT

> macit <ilona.er...@gmail.com> wrote in news:3c0b6853-4092-4690-aefe-
> b9d4eaabf...@q21g2000hsa.googlegroups.com:
[quoted text clipped - 40 lines]
> ALWAYS use ? placeholders. That is a pivilege to you as a java-programmer.
> Leave the SQL-injection to those pesky php-guys.

Donkey Hot, thank you for the quick reply,
particularly for the "sql injection' info which seems important.
(havn't heard about it yet - just startet with sql two weeks ago, so
thank you verry much for the hint!)

Reply to this Message

Arne Vajhøj - 06 Feb 2008 03:17 GMT

> ALWAYS use ? placeholders. That is a pivilege to you as a java-programmer.
> Leave the SQL-injection to those pesky php-guys.

Or tell them to read
http://www.php.net/manual/en/function.mysqli-prepare.php !

Arne

Reply to this Message

How to insert a string that contains ' or ,

Free Magazines