英文:
Sanitize input before passing it as a variable to stored procedure from java
问题
我有一个Java代码，调用存储过程并将搜索结果返回到Web浏览器，我面临的问题是，该过程接受来自浏览器的参数，即使我在Java中使用了PreparedStatement，用户输入也仅在调用过程时进行了清理，但一旦传递的值进入存储过程，它就再次容易受到SQL注入攻击。
以下是代码片段：
Java代码
stmt = con.prepareStatement("call myProcedure(?,?)");
...
rs = stmt.executeQuery();
我的MySQL存储过程：
CREATE PROCEDURE myProcedure(IN userinput1 VARCHAR(255), IN unserinput2 VARCHAR(255))
BEGIN
SELECT
*
FROM
myTable
WHERE
col1 = userinput1
AND col2 = userinput2
ORDER BY id;
END$$
DELIMITER ;
现在，如果我重新设计我的过程，可以解决这个问题：
CREATE PROCEDURE myProcedure(IN userinput1 VARCHAR(255), IN unserinput2 VARCHAR(255))
BEGIN
SET @query = concat("SELECT * FROM myTable WHERE col1 = ? and col2 = ?");
SET @a = userinput1;
SET @b = unserinput2;
PREPARE stmt FROM @query;
EXECUTE stmt USING @a, @b;
END$$
DELIMITER ;
但我的问题是，我正在处理的应用程序已经有很多以这种方式设计的过程和函数，我不想逐个重新设计所有过程。
是否有一种特定的方式，可以在将输入传递给过程本身之前对其进行预清理。
同时，过滤掉SQL关键字不是一个选项，因为有很多代码允许自由文本输入，这是一个旧的多用途存储过程实现，除了可注入的情况外，它工作得很好。
英文:
I have a java code that calls a stored procedure and return a search result to the web browser, the problem i am facing is that the procedure accepts parameter from the browser, now even if I use a PreparedStatement in java the user input is only sanitized for the call of the procedure but once the passed value enters the stored procedure, it is again vulnerable to SQL Injection,
here is a code Snippets
Java code
stmt=con.prepareStatement("call myProcedure(?,?)");
.
.
rs = stmt.executeQuery();
my mysql Procedure:-
CREATE PROCEDURE myProcedure(IN userinput1 VARCHAR(255), IN unserinput2 VARCHAR(255))
BEGIN
SELECT
*
FROM
myTable
where
col1 = userinput1
and col2 = userinput2
ORDER BY id;
END$$
DELIMITER ;
Now I can solve this issue if I redesign my procedure as such :-
CREATE PROCEDURE myProcedure(IN userinput1 VARCHAR(255), IN unserinput2 VARCHAR(255))
BEGIN
SET @query = concat(“SELECT * FROM myTable WHERE col1 = ? and col2 = ?“);
SET @a = userinput1;
SET @b = unserinput2;
PREPARE stmt from @query;
EXECUTE stmt using @a, @b;
END$$
DELIMITER ;
But my issue is, the application I am working on has already has lot of procedures and function designed as such and I don't want to have to go in and redesign all of the procedure one by one.
Is there any particular way where i could pre-sanitize the input before passing it to the procedure itself.
Also filtering out SQL keywords are not an option as there are several codes that allows free text, it's an old implementation of multi-purpose stored procedure that works perfectly beside the injectable scenario.
答案1
得分: 1
SQL注入，
通常在语句中会出现SQL关键词，比如["master", "truncate", "insert", "select", "delete", "update", "declare", "alter", "drop"]，您需要过滤您的参数，如果包含了这些关键词，向浏览器显示错误提示。
英文:
SQL Injection,
Usually there are SQL keywords in statements, such as [ "master", "truncate", "insert", "select", "delete", "update", "declare", "alter", "drop"],You need to filter your parameters，If the keyword is included, give an error prompt to the browser
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论