让SQL执行的问题 [英] Problems getting SQL to execute

问题描述

在我的项目中，我想从数据库中的另一个表中将数据插入到表中



我使用的查询如下

< br $>

In my project I want to insert data to a table from another table in the database

I used query as follows

String st = "select code from state where name='" + this.statename.Text  + "'";
                            String str = "insert into branch(Code,Name,metro,state)values ('" + this.txtbranchcode.Text + "','" + this.txtbranchname.Text + "','" + this.cmbmetro.SelectedIndex + "','" + st + "')";

但编译时没有错误

在运行时它在卡纳塔克邦显示错误为语法错误



这里Karnataka意味着我的statename.text



请更正我错误

but compile time no error
at run time it is showing error as syntax error at Karnataka

here Karnataka means my statename.text

pls correct me error

推荐答案

我同意以前的说法swer认为这是一种非常危险的方法。如果允许用户在`this.statename.Text`中输入值，则可以键入

'; DROP DATABASE; -

引号关闭打开引号，使其成为空字符串，分号终止语句，然后SQL引擎将DROP DATABASE作为新语句处理。哎哟！

DROP DATABASE之后的`--`使得该行的其余部分成为评论。看看这个网站：http：//michaeldaw.org/sql-injection-cheat-sheet



无论如何...



我认为你在语法上出错的地方是你在insert语句中的select语句周围放置单引号，从而将它从select语句转换为字符串文字。即：

值（...，'从名称='卡纳塔克邦'的州选择代码）

此外，由于它已经有单引号，它变为语法正确。



我的SQL有点生疏，但我认为你不能在VALUES（）中有select语句。我认为他们必须是文字。

试试这个。它使用SELECT插入表中：

I agree with the previous answer that this is a very dangerous approach. If the user is allowed to enter a value into `this.statename.Text` they might type
'; DROP DATABASE; --
The quote closes the open quote making it an empty string, the semicolon terminates the statement and SQL engine then processes the DROP DATABASE as a fresh statement. Ouch!
The `--` following the DROP DATABASE makes the rest of the line a comment. Check out this site: http://michaeldaw.org/sql-injection-cheat-sheet

Anyway...

I think where you're going wrong syntactically is that you are putting single quotes around your select statement in the insert statement, thereby turning it from a select statement into a string literal. I.e.:
values( ..., 'select code from state where name='Karnataka'')
Furthermore, since it already has single quotes in it, it becomes syntactically correct.

My SQL is a bit rusty but I don't think you can have select statements inside VALUES(). I think they have to be literals.
Try this instead. It uses a SELECT to insert into the table:

String str = "INSERT INTO branch(Code,Name,metro,state)"
+ " SELECT '" + this.txtbranchcode.Text + "','" 
+ this.txtbranchname.Text + "','" 
+ this.cmbmetro.SelectedIndex + "'," 
+ " code FROM state WHERE name='" + this.statename.Text + "'";

更好的方法是验证州名（它有只有勒tters，没有引号或分号等）。更好的方法是将其作为参数化查询发送 - 这可以避免所有所谓的SQL注入攻击。

Better would be to validate the statename (that it has only letters and no quotes or semicolons etc). Better still would be to send it as a parameterised query - this avoids all the so called SQL injection attacks.

从州名选择代码，其中name ='Karnataka'



人们以自由文本的形式进入州是一种令人讨厌的事情，你正在查找随之而来的代码。您应该使用调试器来获取SQL字符串，然后在SQL Server中运行它以获取更详细的错误和/或确认SQL是正确的。你可能需要在[]中加上名字之类的东西，因为它们可能是保留字。



我仍​​然认为你的第二行会破产。要么就是这样，要么你的数据库设计真是废话。就像你的其他代码一样（我看到你仍然不关心这段代码的安全性，或者关于软件设计的任何基本原理）



我已经完成了一些测试，我上面输入的SQL（应该生成的）在名为state的表上可以正常工作，其中包含名为code和name的字段。也许您需要发布数据库的方案（通过编辑您的帖子）。很明显你不知道自己在做什么，所以很难弄清楚你做错了什么，可能是什么。

select code from state where name='Karnataka'

It is kind of nasty that people are entering a state as free text and you're looking up the code that goes with it. You should use the debugger to get the SQL string, then run it in SQL Server to get more detailed errors and/or confirm that the SQL is right. You may need to put things like name in [], because they may be reserved words.

I still think your second line is going to break. Either that, or your database design is really crap. Just like the rest of your code ( I see you still don't care about how secure this code is, or about any sort of basic principles of software design )

I've done some testing, and the SQL I typed in above, which is what should be generated, will work fine on a table called state, with fields called code and name. Perhaps you need to post the scheme of your DB ( by editing your post ). It's clear you have no idea what you're doing, so it's very hard to work out what you're doing wrong exactly, it could be anything.

这篇关于让SQL执行的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！