插入数据时收到错误为 - >字符串'，0）'后面的未闭合引号。 “，0”附近的语法不正确。 [英] While inserting data getting error as--> Unclosed quotation mark after the character string ',0)'. Incorrect syntax near ',0)'.

问题描述

<ASP:DATAGRID id="dgMenuSubItems" ShowFooter="True"  runat="server" 

                    DataKeyField="SubItemId" AutoGenerateColumns="False"

                    CssClass="datagrid_style" CellPadding="3" Font-Bold="True" ForeColor="#000099" BackColor="White"

                    Width="100%" EnableViewState="True" AllowSorting="True" 

                    >
                    <HeaderStyle HorizontalAlign="Center" CssClass="datagrid_header_style" VerticalAlign="Middle"></HeaderStyle>
                    <SelectedItemStyle CssClass="datagrid_selecteditem_style"></SelectedItemStyle>
                    <itemstyle horizontalalign="Center" cssclass="datagrid_item_style" verticalalign="Middle"></itemstyle>
                    <alternatingitemstyle cssclass="datagrid_alternatingitemstyle"></alternatingitemstyle>
                    <columns>
                        <asp:templatecolumn headertext="SubItemName" sortexpression="SubItemName" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:Label ID="lblSubItemName"  runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemName","") %>'>
                                
                            </itemtemplate>
                            <footerstyle horizontalalign="Center"></footerstyle>
                            <footertemplate>
                                <asp:TextBox MaxLength="100" SkinID="Full" ID="txtSubItemNameF" Runat="server" Text="">
                            </footertemplate>
                            <edititemtemplate>
                                <asp:TextBox MaxLength="100" SkinID="Full" ID="txtSubItemNameE" Runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemName","") %>'>
                                
                            </edititemtemplate>
                        </asp:templatecolumn>
                        <asp:templatecolumn headertext="Price" sortexpression="SubItemPrice" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:Label ID="lblSubItemPrice"  runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemPrice","") %>'>
                                
                            </itemtemplate>
                            <footerstyle horizontalalign="Center"></footerstyle>
                            <footertemplate>
                                <asp:TextBox MaxLength="20" ID="txtSubItemPriceF" Runat="server" Text="">
                            </footertemplate>
                            <edititemtemplate>
                                <asp:TextBox MaxLength="20" ID="txtSubItemPriceE" Runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemPrice","") %>'>
                                
                            </edititemtemplate>
                        </asp:templatecolumn>


                         <asp:templatecolumn headertext="SortOrder" sortexpression="SubItemPrice" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:Label ID="lblSubItemSortOrder"  runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemSortOrder","") %>'>
                                
                            </itemtemplate>
                            <footerstyle horizontalalign="Center"></footerstyle>
                            <footertemplate>
                                <asp:TextBox MaxLength="10" ID="txtSubItemSortOrderF" Runat="server" Text="">
                            </footertemplate>
                            <edititemtemplate>
                                <asp:TextBox MaxLength="10" ID="txtSubItemSortOrderE" Runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemSortOrder","") %>'>
                                
                            </edititemtemplate>
                        </asp:templatecolumn>



                        <asp:templatecolumn headertext="Modify" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:LinkButton ID="lnkSubEdit"  runat="server" Text="<img border=0 src=images/dg_Edit.gif alt=edit>"

                                    CommandName="Edit" CausesValidation="False">
                            </itemtemplate>
                            <edititemtemplate>
                                <ASP:LinkButton ID="lnkSubUpdate"  runat="server" Text="<img border=0 src=images/dg_OK.gif alt=save/update>"

                                    CommandName="Update" CausesValidation="False">  
                                <ASP:LinkButton ID="lnkSubCancel"  runat="server" Text="<img border=0 src=images/dg_Cancel.gif alt=cancel>"

                                    CommandName="Cancel" CausesValidation="False">
                            </edititemtemplate>
                        </asp:templatecolumn>
                        <asp:templatecolumn headertext="Remove" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:LinkButton ID="lnkSubDelete"  runat="server" Text="<img border=0 src=images/dg_Delete.gif alt=delete>"

                                    CommandName="Delete" CausesValidation="False">
                            </itemtemplate>
                            <footerstyle horizontalalign="Center"></footerstyle>
                            <footertemplate>
                                <asp:Button ID="btnSubAddRow" Runat="server" Text="Add New" CommandName="AddANewRow">
                            </footertemplate>
                        </asp:templatecolumn>
                    </columns>

protected void dgMenuSubItems_ItemCommand(object source, DataGridCommandEventArgs e)
        {
            try
            {
                if (e.CommandName == "AddANewRow")
                {
                    string strItemId = this.dgMenuItems.DataKeys[this.dgMenuItems.SelectedIndex].ToString();
                    string strItem = "";
                    TextBox txtSubItemNameF = e.Item.FindControl("txtSubItemNameF") as TextBox;
                    string strPrice = "";
                    TextBox txtSubItemPriceF = e.Item.FindControl("txtSubItemPriceF") as TextBox;
                    if ((txtSubItemNameF != null) && (txtSubItemPriceF != null))
                    {
                        string strQueryMax = "Select Max(SubItemId) from tbl_MenuSubItems";
                        int nMaxItem = Convert.ToInt32(clsADO.getSingleRecord(strQueryMax));

                        strItem = txtSubItemNameF.Text;
                        strPrice = txtSubItemPriceF.Text;

                        strItem = strItem.Replace("''", "''");

                        string strQuery = "Insert into tbl_MenuSubItems values (" + (nMaxItem + 1) + ",'" + strItem + "','" + strPrice + "'," + strItemId + ")";

                        clsADO.executeNonQuery(strQuery);
                    }
                    else
                    {
                        lblError.Text = "Error finding the SubItem";
                    }

                    this.Rebuild_Sub_Display();
                }
            }
            catch (Exception ex)
            {
                lblError.Text = ex.Message;
            }
        }

推荐答案

Check the created strQuery string.



This line should probably handle quote characters in the item string but will effectively do nothing (replaces with the same characters):

Check the created strQuery string.

This line should probably handle quote characters in the item string but will effectively do nothing (replaces with the same characters):

strItem = strItem.Replace("''", "''");

To perform the required replacement it should be:

To perform the required replacement it should be:

strItem = strItem.Replace("'", "''");

[/EDIT]

[/EDIT]

Your code is vulnerable to SQL Injection[^].



NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.



By fixing this critical security vulnerability in your code, you will also fix the error.

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

By fixing this critical security vulnerability in your code, you will also fix the error.

using (var connection = new SqlConnection("-YOUR CONNECTION STRING HERE-"))
using (var command = new SqlCommand("INSERT INTO tbl_MenuSubItems VALUES(1 + IsNull((SELECT Max(SubItemId) FROM tbl_MenuSubItems), 0), @Item, @Price, @ItemId)"))
{
    command.Parameters.AddWithValue("@Item", txtSubItemNameF.Text);
    command.Parameters.AddWithValue("@Price", txtSubItemPriceF.Text);
    command.Parameters.AddWithValue("@ItemId", dgMenuItems.DataKeys[dgMenuItems.SelectedIndex]);
    
    connection.Open();
    command.ExecuteNonQuery();
}

---

Everything you wanted to know about SQL injection (but were afraid to ask) |特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

SQL注入攻击机制Pluralsight [ ^ ]

---

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
SQL injection attack mechanics | Pluralsight [^]

这篇关于插入数据时收到错误为 - >字符串'，0）'后面的未闭合引号。 “，0”附近的语法不正确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！