SQL Server字段值存储问题及ASPX.CS后台插入代码咨询

Hey there! Let's work through your problem together. First off, your current code has two big issues: it’s vulnerable to SQL injection attacks, and it doesn’t handle the default values you specified for different data types. Let’s fix both while making sure your data gets stored correctly in SQL Server 2012.

1. 先明确需求对应的处理逻辑 #

Based on your request, we need to handle each data type like this:

• Int类型列: If no valid value is provided, store 0
• 字符串类型列: If the input is empty/blank, store SQL NULL (not an empty string '')
• 日期类型列: If no valid date is provided, store 2001-01-01

2. 核心优化：替换字符串拼接为参数化查询 #

Your current code directly concatenates values into the SQL string, which is extremely risky—it’s a perfect target for SQL injection attacks. Using SqlParameter not only fixes this security hole but also makes data type conversion much cleaner.

3. 实现各类型字段的默认值处理 #

We’ll add logic to check each property value before assigning it to a parameter:

• For Int fields (like Debit/Credit): Fall back to 0 if the value is null or invalid
• For String fields (like BillNo/ChequeNo): Use DBNull.Value (maps to SQL NULL) if the string is empty/blank
• For Date fields (like VrDate/CreationDate): Fall back to 2001-01-01 if the date is null or invalid

4. 优化后的完整VoucherSubmit方法 #

public class InsertData {
    public void VoucherSubmit() {
        // Use 'using' to auto-release the connection (no need for manual Close())
        using (var dbConnection = new myconection().GetConnection()) {
            VoucherForm vr = new VoucherForm();
            
            // --------------------------
            // Handle String type fields
            // --------------------------
            string vrType = string.IsNullOrWhiteSpace(vr.getvouchertype()) ? null : vr.getvouchertype();
            string createdBy = string.IsNullOrWhiteSpace(vr._createdby) ? null : vr._createdby;
            string submittedBy = string.IsNullOrWhiteSpace(vr._submittedby) ? null : vr._submittedby;
            string approvedBy = string.IsNullOrWhiteSpace(vr._approvedby) ? null : vr._approvedby;
            string paymentMethod = string.IsNullOrWhiteSpace(vr._method) ? null : vr._method;
            string billNo = string.IsNullOrWhiteSpace(vr._billno) ? null : vr._billno;
            string chequeNo = string.IsNullOrWhiteSpace(vr._chequeno) ? null : vr._chequeno;
            string demandNo = string.IsNullOrWhiteSpace(vr._demandno) ? null : vr._demandno;
            string branch = string.IsNullOrWhiteSpace(vr._branch) ? null : vr._branch;
            string accountDebit = string.IsNullOrWhiteSpace(vr._accountdebit) ? null : vr._accountdebit;
            string accountCredit = string.IsNullOrWhiteSpace(vr._accountcredit) ? null : vr._accountcredit;
            string description = string.IsNullOrWhiteSpace(vr._description) ? null : vr._description;
            
            // --------------------------
            // Handle Date type fields
            // --------------------------
            DateTime vrDate = vr._date.HasValue ? vr._date.Value : new DateTime(2001, 1, 1);
            DateTime creationDate = vr.getvoucher_creationdate().HasValue 
                ? vr.getvoucher_creationdate().Value 
                : new DateTime(2001, 1, 1);
            DateTime submittedDate = vr._submitteddate.HasValue 
                ? vr._submitteddate.Value 
                : new DateTime(2001, 1, 1);
            DateTime approvedDate = vr._approveddate.HasValue 
                ? vr._approveddate.Value 
                : new DateTime(2001, 1, 1);
            
            // --------------------------
            // Handle Int type fields
            // --------------------------
            int debitAmount = vr._debit.HasValue ? vr._debit.Value : 0;
            int creditAmount = vr._credit.HasValue ? vr._credit.Value : 0;
            
            // --------------------------
            // Parameterized SQL Query
            // --------------------------
            string insertQuery = @"INSERT INTO Voucher(
                                    VrType, VrDate, CreatedBy, CreationDate, SubmittedBy, SubmittedDate,
                                    ApprovedBy, ApprovedDate, Method, BillNo, ChequeNo, DemandNo, Branch,
                                    AccountDebit, AccountCredit, Debit, Credit, Description
                                ) VALUES (
                                    @VrType, @VrDate, @CreatedBy, @CreationDate, @SubmittedBy, @SubmittedDate,
                                    @ApprovedBy, @ApprovedDate, @Method, @BillNo, @ChequeNo, @DemandNo, @Branch,
                                    @AccountDebit, @AccountCredit, @Debit, @Credit, @Description
                                )";
            
            using (SqlCommand cmd = new SqlCommand(insertQuery, dbConnection)) {
                // Add parameters with proper default values
                cmd.Parameters.AddWithValue("@VrType", vrType ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@VrDate", vrDate);
                cmd.Parameters.AddWithValue("@CreatedBy", createdBy ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@CreationDate", creationDate);
                cmd.Parameters.AddWithValue("@SubmittedBy", submittedBy ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@SubmittedDate", submittedDate);
                cmd.Parameters.AddWithValue("@ApprovedBy", approvedBy ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@ApprovedDate", approvedDate);
                cmd.Parameters.AddWithValue("@Method", paymentMethod ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@BillNo", billNo ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@ChequeNo", chequeNo ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@DemandNo", demandNo ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@Branch", branch ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@AccountDebit", accountDebit ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@AccountCredit", accountCredit ?? DBNull.Value);
                cmd.Parameters.AddWithValue("@Debit", debitAmount);
                cmd.Parameters.AddWithValue("@Credit", creditAmount);
                cmd.Parameters.AddWithValue("@Description", description ?? DBNull.Value);
                
                dbConnection.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}

5. 额外注意事项 #

• Using Statements: Wrapping the connection and command in using ensures resources are automatically released, preventing connection leaks.
• Data Type Matching: Double-check that the parameter types match your SQL Server table’s column types (e.g., if Debit is a decimal instead of int, adjust the code accordingly).
• Safe Parsing: If your VoucherForm uses string properties for dates/numbers, use TryParse methods to avoid runtime exceptions (e.g., DateTime.TryParse(vr._date, out var vrDate)).
• Empty String vs NULL: If you need to store empty strings ('') instead of SQL NULL for text fields, replace null with "" in the string handling logic.

内容的提问来源于stack exchange，提问作者Muhammad Saqib