Announcement

Collapse
No announcement yet.

SQL_Tools INSERT INTO problem

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Brian Chirgwin
    replied
    Originally posted by Michael Mattias View Post
    It's a little more work at the source code level, but you can avoid all the "single quotes here, no quotes there" stuff by using a parameterized statement and binding the values.
    This also protect against SQL injections. i.e. Where the user can type an entry and cause SQL to execute including

    deleting all data
    changing data
    login with admin rights
    capture a copy of all data

    Leave a comment:


  • Michael Mattias
    replied
    Code:
    IF TRIM$(s) = "" THEN
        FUNCTION = "NULL"
        ......
    Depending on application, a string of all spaces may not be the same thing as NULL.

    Leave a comment:


  • Shawn Anderson
    replied
    I use a sql prep function like this:

    Code:
    FUNCTION sqlPrep(s AS STRING) AS STRING      
    
      IF TRIM$(s) = "" THEN
        FUNCTION = "NULL"
      ELSE    
        REPLACE $sq WITH $sq+$sq IN s
        FUNCTION = $sq+s+$sq     
      END IF    
    
    END FUNCTION
    v="shawn"
    sqlStatement="insert into tableName(myName) values("+sqlPrep(v)+")"

    Leave a comment:


  • Michael Mattias
    replied
    It's a little more work at the source code level, but you can avoid all the "single quotes here, no quotes there" stuff by using a parameterized statement and binding the values.

    Code:
    sql = "INSERT INTO tablename (col1, col2, col3)  VALUES (?, ?, ?) "
    
       Sql_Prepare (or whatver the SqlTools syntax is) statement
       FOR n = 1 TO 3 
          Bind Parameter 'n' to statement with a bind variable 
       NEXT
       FOR each row to be inserted 
              param1 = value1       ' params and values are "natural" datatypes 
              param2 = value2      ' that is, the same datatypes as the rest of your program 
              param3  = value3 
             SQL_Execute  statement 
        NEXT
        Clean up here
    If there is only one row to insert, the amount of work done by the DBMS is a wash.... since a direct execute does a prepare and binds the values from the text of the sql statement anyway.....
    ... but if you have many rows to insert, preparing the statement and using bound parameters is way more efficient, since the DBMS only has to parse and prepare the statement ONCE.

    Hell, it's a thought...

    MCM
    Last edited by Michael Mattias; 24 Nov 2008, 08:47 AM.

    Leave a comment:


  • Eric Pearson
    replied
    Barry, FWIW, I wasn't trying to give you a hard time, I was pointing out the similarities between PB syntax and SQL syntax as a way to help you sort things out. Of course it's the differences that will getcha...

    -- Eric

    Leave a comment:


  • Barry Erick
    replied
    Never mind... fixed.. see below


    Now it's a Error 07001 .. missing paramater.. expects 1

    Code:
        sqls = "DELETE FROM VC WHERE AudioIndex = " + "Indexx"
                    ? sqls
                    res = SQL_Stmt (%SQL_STMT_IMMEDIATE, sqls)
    VC is the table (all inserts are fine) and AudioIndex is the numeric column and the first column. The index passed IS correct. res returns -1. I have tried encosing the field in single quotes, but looking at the QSL_Tools Del routine, it is not used there so I leave it here. If I change "Indexx" to "'sIndex'", which is the index in string form that Index is derived from with VAL
    Code:
    Indexx = val(sIndex)
    Never mind... brain * again. Fixed
    Last edited by Barry Erick; 23 Nov 2008, 10:15 PM. Reason: fixed

    Leave a comment:


  • Barry Erick
    replied
    Originally posted by Eric Pearson View Post
    Barry --



    Old friend, you have been using BASIC even longer than I have. Does that rule... ring a bell?


    -- Eric
    Yes, it is somthing I knew. Problem is, following 3 surgeries in one week (a few years ago), I forgot many things and have to re-learn much. It's just little rules that are locked in somewhere and have to be knocked loose.

    Thanks for the (obvious) tip!

    Leave a comment:


  • Brian Chirgwin
    replied
    Originally posted by Barry Erick View Post
    Ok, that works... single quote around strings (or items going to a text filed BUT NOT GOING TO A NUMERIC field.
    Now to find the chaulk to write this 100 times.
    Also you need to take care of special characters that have special meaning in SQL. Similar to text that contains greater than/less than (< >) that is being placed in HTML.

    In SQL apostrophes need to be replaced
    Code:
       INSERT INTO Contact (lastname) VALUES ('O'Brian')
    Should be corrected to

    Code:
       INSERT INTO Contacts (lastname) VALUES ('O''Brian')
    That's is two single quotes after the O, not a double quote.

    Leave a comment:


  • Michael Mattias
    replied
    >There is a empy item for filename in there that I failed to show:
    >(5,,'D:\DRIVE E ...

    ===>>> "(5, NULL, 'D:\Drive E...."

    MCM

    Leave a comment:


  • Eric Pearson
    replied
    Barry --

    quotes around strings (...) but not (...) numerics
    Old friend, you have been using BASIC even longer than I have. Does that rule... ring a bell?

    Seriously, all you have to remember is that within SQL statements, strings need single quotes. Some database drivers are more forgiving than others; they will convert the string '1' to the numeric value 1, and so on. But overall if you put (single) quotes around strings and don't put them around numerics, you'll do fine.

    -- Eric

    Leave a comment:


  • Barry Erick
    replied
    Originally posted by Eric Pearson View Post
    Barry --

    > Single quotes inside the double quotes

    You definitely need single quotes around strings in a SQL statement. The final result should look something like...
    Code:
    INSERT INTO VC (AudioIndex,FileName,LongFilename) VALUES(5,'C:\DRIVE E\F\Checked MP3 Files\5th Dimension\01 Up-Up and Away','The 5th Dimension.mp3')
    ...although I don't think those are quite the right strings. You need 3 values (AudioIndex,FileName,LongFilename) and 'The 5th Dimension.mp3' doesn't look like a LongFileName to me.

    -- Eric Pearson, Perfect Sync, Inc.
    There is a empy item for filename in there that I failed to show:
    (5,,'D:\DRIVE E ...
    I'll try the single quotes again. I had asciiz strings in there first and have strings, now.

    Ok, that works... single quote around strings (or items going to a text filed BUT NOT GOING TO A NUMERIC field.
    Now to find the chaulk to write this 100 times.
    Last edited by Barry Erick; 20 Nov 2008, 07:40 AM.

    Leave a comment:


  • Andre Smit
    replied
    Strings should be inserted as strings - use single quotes as in
    Code:
    sqlstmnt = "INSERT INTO VC (AudioIndex,Filename,LongFilename) VALUES ('" + ai(1) +"','" +_
            ai(2) + "','" + ai(3) +"')"

    Leave a comment:


  • Eric Pearson
    replied
    Barry --

    > Single quotes inside the double quotes

    You definitely need single quotes around strings in a SQL statement. The final result should look something like...
    Code:
    INSERT INTO VC (AudioIndex,FileName,LongFilename) VALUES(5,'C:\DRIVE E\F\Checked MP3 Files\5th Dimension\01 Up-Up and Away','The 5th Dimension.mp3')
    ...although I don't think those are quite the right strings. You need 3 values (AudioIndex,FileName,LongFilename) and 'The 5th Dimension.mp3' doesn't look like a LongFileName to me.

    -- Eric Pearson, Perfect Sync, Inc.

    Leave a comment:


  • Barry Erick
    started a topic SQL_Tools INSERT INTO problem

    SQL_Tools INSERT INTO problem

    I'm having a heck of a time with syntax errors. If I make the statment


    Code:
    sqlstmnt = "INSERT INTO VC (AudioIndex) VALUES (" + ai(1) +")"
    It will insert this column.

    But, If I try to add more fields, it won't:

    Code:
    sqlstmnt = "INSERT INTO VC (AudioIndex,Filename,LongFilename) VALUES (" + ai(1) +"," +_
            ai(2) + "," + ai(3) +")"
    The column names are correct, including case. There are actually 47 columns, but as I understand it, I do not have to send all.

    The last example gives the sql syntax error in the statement. The statement comes out as such:
    Code:
    INSERT INTO VC (AudioIndex,FileName,LongFilename) VALUES(5,,C:\DRIVE E\F\Checked MP3 Files\5th Dimension\01 Up-Up and Away = The 5th Dimension.mp3)
    I've also tried placing "','" (Single quotes inside the double quotes as in the sample SQL_Tools programs to no avail. ai() are string valuse.. The database fields referenced ate Numeric, TEXT and TEXT
Working...
X