Insert from java into SQL Server appends 'null'

Question

I'm trying to insert values into a SQL database from within Java. This works fine, except for some of the values. Eg, when I insert "foo" it appends null at the start so it becomes "nullfoo". If I insert the same statement in SQL Server Management Studio this doesn't happen.

To be sure: I print the string before inserting it and it reads "foo".

My insert code:

statement.execute("INSERT INTO " + settings.getProperty("table") + " VALUES ('" + value1+ "', '" + value2 + "', '" + value3 + "')");

Jon Skeet · Accepted Answer · 2011-11-16 09:59:19Z

10

You're concatenating values into the SQL statement. If any of those references (value1, value2 etc) are null, then those will be converted into the string "null" as part of concatenation.

The correct fix for this is not to change the way you're doing validation - it's to stop putting the values into the SQL statement itself. Use PreparedStatement with parameterized SQL and set parameter values instead.

Benefits:

You won't get "null" inserted any more
You won't be vulnerable to SQL injection attacks any more (you are now)
When inserting non-text data you won't need to worry about problematic conversions (this is particularly relevant for date/time fields)
Your code will be clearer, as you'll be separating the code (SQL) from the data (parameter values)
Your prepared statement query plan can be cached by the server, so it may perform faster

answered Nov 16, 2011 at 9:59

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

Freek8 Over a year ago

Thanks for the quick reply. I made sure though that the references are not null (by printing the query before executing it), still it appends null at the start. I'll have a look at PreparedStatement in the meantime

Jon Skeet Over a year ago

@Freek8: Please try assigning the value to a local variable first, and show us the log of that variable. I'm 99% sure you're just misdiagnosing this - but using parameterized SQL is definitely the way forward anyway.

Freek8 Over a year ago

I´ll use the parameterized way. Just out of curiosity, if I assign it to a local variable and print that it reads: INSERT INTO table VALUES ('', '', '', '', '2129678021')

Jon Skeet Over a year ago

@Freek8: And how are you then diagnosing the "null"? Is it perhaps in your reading code?

Freek8 Over a year ago

I tried doing it with preparedStatement (see question) but it still gives me null - values :/

|

Rafa de Castro · Accepted Answer · 2011-11-16 10:01:26Z

1

You should use variable binding in your SQL

http://decipherinfosys.wordpress.com/2007/08/29/bind-variables-usage-parameterized-queries-in-sql-server/

It's easier to check for errors.

In your case you are probably adding null+"foo" so you get nullfoo.

answered Nov 16, 2011 at 10:01

Rafa de Castro

2,5241 gold badge24 silver badges44 bronze badges

Collectives™ on Stack Overflow

Insert from java into SQL Server appends 'null'

2 Answers 2

16 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related