Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
BrownZeus

Java and Databases

Recommended Posts

Posted · Original PosterOP

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!

Link to post
Share on other sites

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).


Crystal: CPU: i7 7700K | Motherboard: Asus ROG Strix Z270F | RAM: GSkill 16 GB@3200MHz | GPU: Nvidia GTX 1080 Ti FE | Case: Corsair Crystal 570X (black) | Storage: 250 GB Crucial BX100 SSD + 2 TB Seagate HDD + 1TB WD Green + 3TB WD Red | PSU: EVGA Supernova G2 1000W | Monitor: Asus VG248QE 24"

Laptop: Dell XPS 13 9360 | CPU: i5 7200U | RAM: 8 GB

Link to post
Share on other sites
Posted · Original PosterOP

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

 

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

Link to post
Share on other sites
Posted · Original PosterOP
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?

Link to post
Share on other sites
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.


Crystal: CPU: i7 7700K | Motherboard: Asus ROG Strix Z270F | RAM: GSkill 16 GB@3200MHz | GPU: Nvidia GTX 1080 Ti FE | Case: Corsair Crystal 570X (black) | Storage: 250 GB Crucial BX100 SSD + 2 TB Seagate HDD + 1TB WD Green + 3TB WD Red | PSU: EVGA Supernova G2 1000W | Monitor: Asus VG248QE 24"

Laptop: Dell XPS 13 9360 | CPU: i5 7200U | RAM: 8 GB

Link to post
Share on other sites
Posted · Original PosterOP

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.

 

Link to post
Share on other sites

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).


Intel i7 5820K (4.5 GHz) | MSI X99A MPower | 32 GB Kingston HyperX Fury 2666MHz | Asus RoG STRIX GTX 1080ti OC | Samsung 951 m.2 nVME 512GB | Crucial MX200 1000GB | Western Digital Caviar Black 2000GB | Noctua NH-D15 | Fractal Define R5 | Seasonic 860 Platinum | Logitech G910 | Sennheiser 599 | Blue Yeti | Logitech G502

 

Nikon D500 | Nikon 200-500 f/5.6 | Nikon 50mm f/1.8 | Tamron 70-210 f/4 VCII | Sigma 10-20 f/3.5 | Nikon 17-55 f/2.8 | Tamron 90mm F2.8 SP Di VC USD Macro | Neewer 750II

Link to post
Share on other sites

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.

 

Link to post
Share on other sites
Posted · Original PosterOP
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!!

Link to post
Share on other sites
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).


Intel i7 5820K (4.5 GHz) | MSI X99A MPower | 32 GB Kingston HyperX Fury 2666MHz | Asus RoG STRIX GTX 1080ti OC | Samsung 951 m.2 nVME 512GB | Crucial MX200 1000GB | Western Digital Caviar Black 2000GB | Noctua NH-D15 | Fractal Define R5 | Seasonic 860 Platinum | Logitech G910 | Sennheiser 599 | Blue Yeti | Logitech G502

 

Nikon D500 | Nikon 200-500 f/5.6 | Nikon 50mm f/1.8 | Tamron 70-210 f/4 VCII | Sigma 10-20 f/3.5 | Nikon 17-55 f/2.8 | Tamron 90mm F2.8 SP Di VC USD Macro | Neewer 750II

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×