I have a java app + SQL server database. DB operation use JDBC with dynamic SQL string. Example:
我有一个java app + SQL server数据库。 DB操作使用JDBC和动态SQL字符串。例:
Select Column from tab where column=StringParm
从选项卡中选择列,其中column = StringParm
StringParam is user input. if the stringParm include apostrophe, Java app will throw exception and said Can't execute the SQL.
StringParam是用户输入。如果stringParm包含撇号,Java应用程序将抛出异常并表示无法执行SQL。
How to resolve this problem with no java code changing?
没有java代码更改如何解决这个问题?
5 个解决方案
#1
Never put user input directly in a SQL query. You need to use a PreparedStatement with parameters. Without changing the Java code, I don't see any way to make this safe.
切勿将用户输入直接放在SQL查询中。您需要使用带参数的PreparedStatement。在不更改Java代码的情况下,我认为没有任何方法可以确保安全。
#2
I'm guessing you construct the SQL in some manner like
我猜你用某种方式构造SQL
String sql = "Select Column from tab where column='" + StringParm + "'";
Or something like it ? If you do that, you're open to all kinds of exploits and you'll also see behavior like you describe, where the resulting string is no longer valid SQL. You'd have to escape the user supplied parameter first.
或类似的东西?如果你这样做,你就会接受各种各样的攻击,你也会看到你所描述的行为,结果字符串不再是有效的SQL。您必须首先转义用户提供的参数。
The best solution is to use PreparedStatements, so you do
最好的解决方案是使用PreparedStatements,这样就可以了
Statement stmt = conn.prepareStatement("Select Column from tab where column=?");
stmt.setString(1,StringParam);
I can't see any quick way of solving your problem without altering any Java code though, bar perhaps escaping/sanitizing the input before it hits your code (e.g. javascript if you're a webapp)
我无法在不改变任何Java代码的情况下看到任何快速解决问题的方法,禁止在输入代码之前转义/清理输入(例如,如果您是webapp,则为javascript)
#3
You should really use java.sql.PreparedStatement to set parameters. The code changes should be minimal and it is less problematic that trying to escape user input.
您应该使用java.sql.PreparedStatement来设置参数。代码更改应该是最小的,并且尝试逃避用户输入的问题较小。
#4
You can not fix this without changing the application. SQL Server can handle quotes, however your application (Java code) is not properly escaping the quotes when you build your dynamic SQL commands. I prefer using stored procedures and passing in parameters, this way there are never any quotes issues or injections.
如果不更改应用程序,则无法解决此问题。 SQL Server可以处理引号,但是在构建动态SQL命令时,应用程序(Java代码)无法正确转义引号。我更喜欢使用存储过程并传入参数,这样就不会有任何引号问题或注入。
#5
You could fix this by putting a trigger in the database to clean up the entry - i.e. when an insert is attempted, instead do proper escaping on the input, and then continue with the new insert input. However, this is the wrong layer and it should probably not be done down there. A much better solution (IMO) is to use a prepared statement, and do variable replacements, letting JDBC do the escape work for you.
您可以通过在数据库中放置一个触发器来清理条目来解决这个问题 - 即在尝试插入时,而是在输入上进行适当的转义,然后继续使用新的插入输入。但是,这是一个错误的层,它可能不应该在那里完成。一个更好的解决方案(IMO)是使用预准备语句,并进行变量替换,让JDBC为您执行转义工作。
#1
Never put user input directly in a SQL query. You need to use a PreparedStatement with parameters. Without changing the Java code, I don't see any way to make this safe.
切勿将用户输入直接放在SQL查询中。您需要使用带参数的PreparedStatement。在不更改Java代码的情况下,我认为没有任何方法可以确保安全。
#2
I'm guessing you construct the SQL in some manner like
我猜你用某种方式构造SQL
String sql = "Select Column from tab where column='" + StringParm + "'";
Or something like it ? If you do that, you're open to all kinds of exploits and you'll also see behavior like you describe, where the resulting string is no longer valid SQL. You'd have to escape the user supplied parameter first.
或类似的东西?如果你这样做,你就会接受各种各样的攻击,你也会看到你所描述的行为,结果字符串不再是有效的SQL。您必须首先转义用户提供的参数。
The best solution is to use PreparedStatements, so you do
最好的解决方案是使用PreparedStatements,这样就可以了
Statement stmt = conn.prepareStatement("Select Column from tab where column=?");
stmt.setString(1,StringParam);
I can't see any quick way of solving your problem without altering any Java code though, bar perhaps escaping/sanitizing the input before it hits your code (e.g. javascript if you're a webapp)
我无法在不改变任何Java代码的情况下看到任何快速解决问题的方法,禁止在输入代码之前转义/清理输入(例如,如果您是webapp,则为javascript)
#3
You should really use java.sql.PreparedStatement to set parameters. The code changes should be minimal and it is less problematic that trying to escape user input.
您应该使用java.sql.PreparedStatement来设置参数。代码更改应该是最小的,并且尝试逃避用户输入的问题较小。
#4
You can not fix this without changing the application. SQL Server can handle quotes, however your application (Java code) is not properly escaping the quotes when you build your dynamic SQL commands. I prefer using stored procedures and passing in parameters, this way there are never any quotes issues or injections.
如果不更改应用程序,则无法解决此问题。 SQL Server可以处理引号,但是在构建动态SQL命令时,应用程序(Java代码)无法正确转义引号。我更喜欢使用存储过程并传入参数,这样就不会有任何引号问题或注入。
#5
You could fix this by putting a trigger in the database to clean up the entry - i.e. when an insert is attempted, instead do proper escaping on the input, and then continue with the new insert input. However, this is the wrong layer and it should probably not be done down there. A much better solution (IMO) is to use a prepared statement, and do variable replacements, letting JDBC do the escape work for you.
您可以通过在数据库中放置一个触发器来清理条目来解决这个问题 - 即在尝试插入时,而是在输入上进行适当的转义,然后继续使用新的插入输入。但是,这是一个错误的层,它可能不应该在那里完成。一个更好的解决方案(IMO)是使用预准备语句,并进行变量替换,让JDBC为您执行转义工作。