none
Double Quotes, Single Quote, Comma, Single Quote, Double Quote RRS feed

  • Question

  • I am sorry if this has been asked.  I cannot find an answer or example on GOOGLE etc.. I have been given a project at work to do.  I am new to SQL programming and DO NOT understand this syntax for the SQL string: "sql += then "Double Quotes, then a Single Quote, then a COMMA, then Single Quote, then Double Quote"

    Or is the syntax   "Single Quote, Double Quote, Comma, Double Quote, Single Quote"
     

    Questions:
     1.  What is the "sql +="  doing??

     2.  Please explain what each thing is doing   "Double Quotes, then a Single Quote, then a COMMA, then Single Quote, then Double Quote"

    Here is my code snippet:

    <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    // build the SQL string to inster data into the database

         sql="INSERT INTO cdf (username, phone, storage, site, dept, quantity, stor_sd_micap, descript, purp_of_move, part_model_serial, condition, process_asset_tool, part_been_exposed, process_name, chem_expose_listing, corrosive, pump_oils, solvents, copper, lead, slurry, arsenic, di_hi_res_water, slur_type, gas_refrig, type_vol_psi, coolant_mixtures, mixture, other, contam_other, ph_btwn_5_9, exposed_gases_cleaned, exposed_liq_chem_clean, debris_removed, liq_removed_capped, gas_purged, ports_sealed, radiation_appr, energy_dissipated, free_from, free_from_batteries, n2_purge, di_water_flush, ipa_wipe_scrub, other_decon, list, pH_test, fluoride_test, hydrogen_test, other_test, test_specify, residue, comments, name_filling_form, date_submit, signature_verify)";

                     sql += " VALUES ('" + username.Trim();

                     sql += "','" + phone.Trim();

                     sql += "','" + storage;

                     sql += "','" + site;

                     sql += "','" + dept.Trim();

                     sql += "','" + quantity.Trim();

                     sql += "','" + stor_sd_micap.Trim();

                     sql += "','" + desc.Trim();

                     sql += "','" + purp_of_move;

                     sql += "','" + test_specify.Trim();

                     sql += "','" + residue.Trim();

                     sql += "','" + comments.Trim();

                     sql += "','" + name_filling_form.Trim();

                     sql += "','" + date_submit+"','')";



    I just do not have a clue what this code is doing. I hope someone can explain in "newbie" terms.

    Many Thanks!!!
    Tuesday, October 27, 2009 1:23 AM

Answers

  • This code is building a SQL INSERT statement by concatenating user-supplied input.  For example

    sql="INSERT INTO cdf (username, phone, ...

    sql += " VALUES ('" + username.Trim();

    sql += "','" + phone.Trim();

    Will result in a final statement like:

    INSERT INTO cdf (username, phone, ...
    VALUES('SuppliedUserName', '555-555-555...

    The double-quotes are used to enclose strings in application code and the single quotes are used to enclose string values within the SQL statement itself.  This nesting of strings is confusing and is one of the reasons that you should instead use a parameterized SQL statement.  Concatenating user-supplied input is a terrible practice for several reasons, including performance and security.  Instead, once should use parameterized SQL Statement:

    sql="INSERT INTO cdf (username, phone, ...
    VALUES(@username, @phone, @storage..."

    And then add the values as SQL Statement parameters.  The details on how to do this depend on the language and API you are using.  See http://weblogs.sqlteam.com/dang/archive/2008/02/18/Why-Parameters-are-a-Best-Practice.aspx for a simple example.



    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, October 27, 2009 3:31 AM
    Moderator

All replies

  • This code is building a SQL INSERT statement by concatenating user-supplied input.  For example

    sql="INSERT INTO cdf (username, phone, ...

    sql += " VALUES ('" + username.Trim();

    sql += "','" + phone.Trim();

    Will result in a final statement like:

    INSERT INTO cdf (username, phone, ...
    VALUES('SuppliedUserName', '555-555-555...

    The double-quotes are used to enclose strings in application code and the single quotes are used to enclose string values within the SQL statement itself.  This nesting of strings is confusing and is one of the reasons that you should instead use a parameterized SQL statement.  Concatenating user-supplied input is a terrible practice for several reasons, including performance and security.  Instead, once should use parameterized SQL Statement:

    sql="INSERT INTO cdf (username, phone, ...
    VALUES(@username, @phone, @storage..."

    And then add the values as SQL Statement parameters.  The details on how to do this depend on the language and API you are using.  See http://weblogs.sqlteam.com/dang/archive/2008/02/18/Why-Parameters-are-a-Best-Practice.aspx for a simple example.



    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, October 27, 2009 3:31 AM
    Moderator
  • Thank you for the information. I will study up on using parametrized SQL statements.  Thank you for clarifying what the quotes are doing. It is confusing!

    Thanks!

    Dave
    Tuesday, October 27, 2009 5:02 AM