r/javahelp • u/fabiopires10 • Apr 12 '21
How can I prevent SQL injection?
I have to concatenate the SQL expression because some variable values come from a XML file so that leaves me open to SQL injection. I was thinking of verifiying if the value of the variables is only a single word(if the value doesn't have spaces). Would it be viable?
@Override
public void execute(Database database) throws CustomChangeException {
try {
//check if there are any errors in the changelog file
checkArgumentsNumber();
boolean tablesFound = false;
boolean columnsAdded = false;
String sqlStatement = "ALTER TABLE NAME ADD COLUMN " +this.getColumnName()+" "+this.getColumnType();
if(notNull){
sqlStatement+=" NOT NULL";
}
if(defaultValue!=null){
sqlStatement+=" DEFAULT ?";
}
if (this.after != null) {
sqlStatement += " AFTER " +this.after;
}
System.out.println("The statement is: "+sqlStatement);
//get tables in the database
JdbcConnection connection = (JdbcConnection) database.getConnection();
DatabaseMetaData metadata;
metadata = connection.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = metadata.getTables(connection.getCatalog(), null, "%", types);
//if the user chose to use a regex
if (this.getRegex() != null) {
Pattern pattern = Pattern.compile(this.getRegex());
while (rs.next()) {
String tableName = rs.getString(3);
Matcher matcher = pattern.matcher(tableName);
boolean matches = matcher.matches();
if (matches) {
tablesFound = true;
String addStatement=sqlStatement.replaceAll("NAME",tableName);
PreparedStatement s = connection.prepareStatement(addStatement);
if (!checkColumnsExists(s, tableName)) {
if(this.defaultValue!=null){
s.setString(1,this.defaultValue);
}
System.out.println("REGEX:"+s.toString());
s.executeUpdate();
columnsAdded = true;
}
}
}
}
checkInvalidInfo(tablesFound, columnsAdded, "All the matching tables already had that column");
} catch (InvalidArgumentsNumberException | SQLException | InvalidInfoException | DatabaseException e) {
throw new CustomChangeException("Error enabling trigger: " + e);
}
}26
u/seanprefect Grumpy Guy Who Remembers Java 2 Apr 12 '21
use parameterized queries instead of direct statements
https://cheatsheetseries.owasp.org/cheatsheets/Query_Parameterization_Cheat_Sheet.html
5
u/fabiopires10 Apr 12 '21
I can't because column name, column type,etc can't be parameterized or else PreparedStatement puts ' ' on it
25
u/seanprefect Grumpy Guy Who Remembers Java 2 Apr 12 '21
if your database is designed in such a way as the end users have to know your column names then i'm afraid you've got bigger fish to fry.
3
u/fabiopires10 Apr 12 '21 edited Apr 12 '21
The objective of the class is to run migrations , so the table and column names need to be known
0
u/denisfalqueto Apr 13 '21
As I understand it, your migrations are not written by users, right? If so, you will not have to worry about sql injections in this front. You only have possibility of sql injections if a user input is used by some sql statement directly without sanitation.
1
u/msx Apr 13 '21
you could use a regular expression that matches allowed identifier for your database implementation. usually something like AZaz09_
1
7
u/hugokhf Apr 12 '21
Look up ‘prepared statement’, that’s the way to do SQL queries in Java
1
u/fabiopires10 Apr 12 '21
if your database is designed in such a way as the end users have to know your column names then i'm afraid you've got bigger fish to fry.
already using it
2
u/makeTechnoGreatAgain Nooblet Brewer Apr 12 '21
Using prepared statement
1
u/fabiopires10 Apr 12 '21
already using it
2
u/MRH2 Intermediate Brewer Apr 12 '21
so, you're safe. Right?
This is PHP, but I assume that you can do the same in Java:
if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) { /* bind parameters for markers */ $stmt->bind_param("s", $city); /* execute query */ $stmt->execute(); /* bind result variables */ $stmt->bind_result($district); /* fetch value */ $stmt->fetch(); printf("%s is in district %s\n", $city, $district); /* close statement */ $stmt->close();1
u/fabiopires10 Apr 12 '21
The problem is that I really do need to concatenate the string because I must receive the parameters from a XML file
10
Apr 12 '21
I fear that if you resort to altering tables directly, there's little else you can do but filter out any unwanted input.
I'll gladly be proven wrong.
7
Apr 12 '21
You should be sanitizing your inputs anyways? It's a pain, but if your inputs are potentially untrusted it's necessary.
If your inputs are trusted and comply with a format from known providers you're in communication with and there's an SLA on format changes to inputs, you can just document & accept the risk.
3
u/Uncle_DirtNap Extreme Brewer Apr 13 '21
There's no platform that allows you to parameterize elements of DDL statements like this. If you don't care about fully supporting your platform's allowances for special characters in object names, you can do some trivial parsing to make sure that there aren't unexpected special characters, but that won't help you if you support the operations that would be injected, and your XML file base is a compromise vector.
Think about little bobby tables... Your concern in that a case like that is someone will introduce or modify an XML file that's supposed to look something like:
<change><operation>add</operation><tabName>fun_table</tabName><colName>new_column</colname></change>to something like:
<change><operation>add</operation><tabName>fun_table</tabName><colName>new_column; ALTER TABLE important_table DROP money_column;</colname></change>but it seems like your system would be open to the more effective exploit of changing:
<change><operation>drop</operation><tabName>fun_table</tabName><colName>legacy_column</colname></change>to:
<change><operation>drop</operation><tabName>important_table</tabName><colName>money_column</colname></change>at which point it's not really about the syntax, it's about the meaning of the objects.
If I had to do this, I would do this using a dry-run database, and ideally pass the diff in front of a human or some other validation process.
1
u/HotRodLincoln Apr 12 '21 edited Apr 12 '21
verifiying if the value of the variables is only a single word
Well, that's only sort of a java question, but does your database server and your java server recognize all the same whitespace as breaks? I mean not just spaces, but new lines, and any unicode bullshit like "Thin Space".
I have a feeling something very wrong is happening here, but you're likely going to find yourself finding the "good characters" and filtering based on being in that set.
From an Americentric view, that probably means filtering out A-Za-z0-9.
You'll probably find yourself slumming in the unicode spec trying to figure out accents and a bunch of other stuff if you need to add other languages or Asian languages.
Depending on the requirements exactly, it may make sense for a human to look at the column names either once per table or per column before adding them.
Also, just for thought:
exec[sp_executesql]N'select''asdf''as[asdf]into[#MyTable]'
SELECT/**/'HelloWorld';
SELECT 'HelloWorld';
Select
'HelloWorld';1
u/khooke Extreme Brewer Apr 12 '21
Always use PreparedStatements
Always use bind parameters
Never dynamically build SQL statements by concatenating user input values into your sql string
PreparedStatements already escape values in your bind parameters to avoid sql injection. Any other user input sanitizing will help prevent other potential vulnerabilities like reflected XSS etc, but PreparedStatements used correctly (e.g. with only bind params and no concatenated values into the sql) will do all the work for you.
1
•
u/AutoModerator Apr 12 '21
Please ensure that:
Your code is properly formatted as code block - see the sidebar (About on mobile) for instructions
You include any and all error messages in full
You ask clear questions
You demonstrate effort in solving your question/problem - plain posting your assignments is forbidden (and such posts will be removed) as is asking for or giving solutions.
Trying to solve problems on your own is a very important skill. Also, see Learn to help yourself in the sidebar
If any of the above points is not met, your post can and will be removed without further warning.
Code is to be formatted as code block (old reddit: empty line before the code, each code line indented by 4 spaces, new reddit: https://imgur.com/a/fgoFFis) or linked via an external code hoster, like pastebin.com, github gist, github, bitbucket, gitlab, etc.
Please, do not use triple backticks (```) as they will only render properly on new reddit, not on old reddit.
Code blocks look like this:
You do not need to repost unless your post has been removed by a moderator. Just use the edit function of reddit to make sure your post complies with the above.
If your post has remained in violation of these rules for a prolonged period of time (at least an hour), a moderator may remove it at their discretion. In this case, they will comment with an explanation on why it has been removed, and you will be required to resubmit the entire post following the proper procedures.
To potential helpers
Please, do not help if any of the above points are not met, rather report the post. We are trying to improve the quality of posts here. In helping people who can't be bothered to comply with the above points, you are doing the community a disservice.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.