locked
SQL Server excludes Blanks in Insert Statement RRS feed

  • Question

  • I´m inserting a nchar value with the SqlClient in C#. On my test machine everything works fine but on the live machine blanks between words are excluded.

    Does anybody know something about that problem?
    Thursday, June 26, 2008 8:44 AM

Answers

  •  

    I think I spotted the problem. in the "this.sql = " line you are setting the value as part of the SQL statement. Since the value may contain special SQL characters, the statement can become wrong.

     

    Please do not provide parameters inline the SQL statement - this opens the application to SQL injection attacks in addition to the fact that it can cause functional problems. To solve this, provide the value as a parameter.

     

    Search MSDN for SqlParameter and use it to provide the value of the tmpdoctype to the SQL. Or, just search in internet for parametrized SQL query code samples.

    Friday, July 25, 2008 4:02 PM

All replies

  • I'm not sure that I completely follow your question.

    By blanks do you mean spaces? So you're attempting to insert 'this is a test' and what actually gets stored is 'thisisatest'?

    Is your application a web application? Can you issue the database calls directly through Management Studio and reproduce the results?

    HTH...

    Joe

    Thursday, June 26, 2008 11:53 AM
  • Yes i mean spaces, i´m inserting 'this is a test' and in the database it looks like 'thisisatest'.

    No the application is a console application and our customer has told me that my develop sql server vm image is equivalent to the live server.

    when i´m trying the application everything works fine, but on the live server it doesn´t
    Thursday, June 26, 2008 12:01 PM
  • How are you executing the insert? Is it through a stored procedure? Embedded SQL in the app?  Can you post the code/

    Can you execute the SQL code directly against the production database (not inside the application) to see if you can reproduce the problem outside of the application?

    Joe

    Thursday, June 26, 2008 5:01 PM
  • I'm setting the value and replace special caracters and the blanks with their ascii codes.

    Code Snippet

    line = "<TXA.2>Krankenblatt Intensiv</TXA.2>";this.tmpdoctype = line;

    line = line.Replace("<TXA.2>", "");
    line = line.Replace("</TXA.2>", "");
    //sonderzeichen
    line = line.Replace("\\", "");
    line = line.Replace("/", "");
    line = line.Replace(":", "");
    line = line.Replace("\"", "");
    line = line.Replace("<", "");
    line = line.Replace(">", "");
    line = line.Replace("|", "");
    line = line.Replace(" ", "' + CHAR(32) + '");


    tmpdoctype = line;


    afterwards i'm checking if this doctype already exists in the db, if not i execute an insert statement with the doctype


    Code Snippet

    this.sql = "select count(1) from doctype where upper(doctype_name) = upper('" + tmpdoctype + "')";


    string tmp_doc_id;

    if (db.get_var(this.sql).ToString() == "0")
    {
          this.sql = "insert into doctype (doctype_name) values ('"+tmpdoctype+"')";
          writeSql(this.sql, false);
          this.db.query(this.sql);
          tmp_doc_id = db.get_var("select doctype_id from doctype where upper(doctype_name) = upper('"+tmpdoctype+"')").ToString();

    }



    i cannot understand why this problem occurs only in the live system!?
    Wednesday, July 2, 2008 9:43 AM
  •  

    I think I spotted the problem. in the "this.sql = " line you are setting the value as part of the SQL statement. Since the value may contain special SQL characters, the statement can become wrong.

     

    Please do not provide parameters inline the SQL statement - this opens the application to SQL injection attacks in addition to the fact that it can cause functional problems. To solve this, provide the value as a parameter.

     

    Search MSDN for SqlParameter and use it to provide the value of the tmpdoctype to the SQL. Or, just search in internet for parametrized SQL query code samples.

    Friday, July 25, 2008 4:02 PM