How to read sql query from a txt-file in Java
You only need to escape your double-quotes in Java string literals. If you're reading the SQL query from a file, Java will have no problem with the double-quotes unescaped in the file.
Take out all the escapes on the double-quotes in your file, and it should work fine.
p.first_name "FirstName",
Also, create your StringBuilder
before your while
loop, so you you don't start over each time, and that you don't read two lines per iteration:
StringBuilder sb = new StringBuilder();
String line;
while ((line = bufferedReader.readLine()) != null)
{
sb.append(line);
}
query = sb.toString();
Additionally, instead of replacing the single-quotes at the end for the session ID value (which would work), use a ?
JDBC placeholder, and use a PreparedStatement
to bind the session ID before you execute the query. That would prevent possible SQL injection attempts, e.g. if sessionID
was the string:
Robert'); DROP TABLE players; --
mrk2
Updated on June 18, 2022Comments
-
mrk2 almost 2 years
I am having some some class that is sending queries to data base. Everything was working fine when the query was in the code, but as it's pretty big, I decided to put it in a file and to read it with buffered reader, but it's not working, I always get this:
java.sql.SQLException: ORA-00900: invalid SQL statement
Here is my query:
SELECT p.first_name \"FirstName\", p.middle_name \"MiddleName\", p.last_name \"LastName\", p.birth_name \"BirthName\", p.mothers_name \"MothersName\", p.nick_name \"NickName\", p.username \"Username\", p.currency_id \"Currency\", p.preferred_language_id \"PreferredLanguage\", p.accepts_email \"AcceptsEmail\", p.birth_date \"BirthDate\", p.hear_about_us \"HeardAboutUs\", p.tax_category_id \"TaxCategory\", p.birth_place \"BirthPlace\", p.accepts_id_verification \"AcceptsIdentityVerification\", p.security_prompt \"SecurityPrompt\", p.gender_id \"Gender\", p.tracking_campaign_name \"TrackingCampaign\", p.accepts_sms \"AcceptsSMS\", p.accepts_promotional_sms \"AcceptsPromotionalSMS\", p.identification_number \"IdentificationNumber\", p.id_verified_id \"IdentificationVerified\", p.security_word \"SecurityWord\", p.ident_manual_verified_until \"IdentificationManualVerified\", p.accepts_chat \"AcceptsChat\", p.frequent_player_level_id \"FrequentPlayerLevel\", p.preferred_comm_channel \"PreferredCommunicationChannel\", p.is_reward_abuser \"IsRewardAbuser\", p.newsletter_id \"Newsletter\", p.accepts_rewards \"AcceptsRewards\", ci.postal_code \"PostalCode\", ci.country_id \"Country\", ci.region \"Region\", ci.email \"Email\", ci.address1 \"Address1\", ci.address2 \"Address2\", ci.address3 \"Address3\", ci.phone1 \"Phone1\", ci.phone2 \"Phone2\", ci.city \"City\", ci.mobile_phone \"MobilePhone\", ci.address_state_id \"AddressVerified\" FROM player p JOIN contact_info ci ON p.CONTACT_INFO_ID = ci.CONTACT_INFO_ID JOIN player_session ps ON p.PLAYER_ID = ps.PLAYER_ID WHERE ps.external_client_session_id = \'$sessionID\'
Here is the code I am using:
String query = ""; try{ BufferedReader bufferedReader = new BufferedReader( new FileReader("templates\\sqlQueries\\GetPlayerDetails.txt") ); while(bufferedReader.readLine()!=null){ query = new StringBuilder().append(query).append(bufferedReader.readLine()).toString(); } } catch (FileNotFoundException e){ e.printStackTrace(); } catch (IOException e){ e.printStackTrace(); } query = query.replace("$sessionID", sessionID);