Java and Databases

[BrownZeus]

Hey friends,

 

I just need to be pointed to a good piece of documentation or a good how to.

 

I'm writing a Java program that needs to read and write to a database.

 

I'm using MariaDB to handle my Databasing, and I already have the connector for it set up. I've used my current source code to write test tables and data to the database.

 

But now what I need to accomplish is writing my input to the database, my input is stored in Java variables, so I need to be able to pass these java variables to my SQL statements.

 

The second thing I need to accomplish is pulling information from the database and displaying it/manipulating it. 

 

As a bonus any information to prevent SQL injection would also be good as well.

 

Thanks in advance!

[tikker]

The SQL statements could be created using some form of string formatting. Then pass that string to your database.

For the SQL injection prevention, you should always sanitize your input before passing it to the database (or before executing any user input in general).

[BrownZeus]

So to write to the data base it would be something like

 

String statement = "Insert into table (c1, c2, c3) Values(" + v1 + "," + v2 + ","+ v3+")";

[BrownZeus]

16 minutes ago, tikker said:

The SQL statements could be created using some form of string formatting. Then pass that string to your database.

For the SQL injection prevention, you should always sanitize your input before passing it to the database (or before executing any user input in general).

and then pass that statement to the database?

[tikker]

3 minutes ago, BrownZeus said:

and then pass that statement to the database?

Yeah, build your string, sanitize it (or vice versa) and then pass it to the database.

[BrownZeus]

Alright, I'll give it a shot

2 minutes ago, tikker said:

Yeah, build your string, sanitize it (or vice versa) and then pass it to the database.

 

[Fetzie]

We use the JOOQ framework at work to let our application talk to the database.

17 minutes ago, BrownZeus said:

So to write to the data base it would be something like

 

String statement = "Insert into table (c1, c2, c3) Values(" + v1 + "," + v2 + ","+ v3+")";

You should use a framework or at least prepared statements to avoid somebody passing SQL commands to your variables and them being interpreted by the DBMS (for example the \copy function in PostgreSQL is particularly fun and doesn't even require write-permissions on the host computer to execute).

[Guest]

just DON'T concatenate sql strings from user input or data - this opens the door for sql injection and is one of the OWASP Top 10 all time entries. DON'T

The way to go is to use PreparedStatement - there are probably hundreds of tutorials (random one) available - as this exists for some time now. PreparedStatements don't build strings, they will send the query and the parameters separately to the database.

Beside just using JDBC for database communication there are some other options. Connections are expensive objects and the transaction and exception handling is pretty typing intense. Usually you would use a connection pool to have connections available when needed (see BoneCP / c3p0 / DBCP). 

Other options would be for example JPA (a framework of data-relational mapping, hibernate probably the most well know implementation). 

Then there are libraries that go closer to looking like SQL but using a Java fluent API, like QueryDSL or JOOQ.

 

A lot of frameworks like spring and spring-data deal with a lot of aspects already (errors, transactions). Baeldung has some very nice tutorials for this. The spring JdbcTemplates are one of my favorite ways to attach databases - its a good fit between being close to SQL but also removing most of the boilerplate code.

 

[BrownZeus]

On 6/20/2018 at 1:30 PM, wemu said:

just DON'T concatenate sql strings from user input or data - this opens the door for sql injection and is one of the OWASP Top 10 all time entries. DON'T

The way to go is to use PreparedStatement - there are probably hundreds of tutorials (random one) available - as this exists for some time now. PreparedStatements don't build strings, they will send the query and the parameters separately to the database.

Beside just using JDBC for database communication there are some other options. Connections are expensive objects and the transaction and exception handling is pretty typing intense. Usually you would use a connection pool to have connections available when needed (see BoneCP / c3p0 / DBCP). 

Other options would be for example JPA (a framework of data-relational mapping, hibernate probably the most well know implementation). 

Then there are libraries that go closer to looking like SQL but using a Java fluent API, like QueryDSL or JOOQ.

 

A lot of frameworks like spring and spring-data deal with a lot of aspects already (errors, transactions). Baeldung has some very nice tutorials for this. The spring JdbcTemplates are one of my favorite ways to attach databases - its a good fit between being close to SQL but also removing most of the boilerplate code.

 

Hey there,

 

I did end up going the Prepared Statement route after I stopped trying to rush and slowed down to understand things.

As far as the connection goes, the SQL server is running on an RPI with a static IP, the person (A family friend) I'm making this software for won't touch or configure any of the settings for it being that he has no idea how to to begin with.

 

Now that I can post to it, I've been looking for a source that I can understand to manipulate a result set.  

 

Thank you for helping and thank you for your suggestion!!

[Fetzie]

On 25/06/2018 at 9:06 PM, BrownZeus said:

Hey there,

 

I did end up going the Prepared Statement route after I stopped trying to rush and slowed down to understand things.

As far as the connection goes, the SQL server is running on an RPI with a static IP, the person (A family friend) I'm making this software for won't touch or configure any of the settings for it being that he has no idea how to to begin with.

 

Now that I can post to it, I've been looking for a source that I can understand to manipulate a result set.  

 

Thank you for helping and thank you for your suggestion!!

Which library are you using to communicate with the DB? You should have some methods for using the output from a database query (they'll save the output in something like a "Record" or "Result" object).