.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Thursday, March 17, 2005
  Quotes & parameters
Saw this question on a newsgroup(microsoft.public.data.oledb ) :

"How can legally include " ' " (single quote) in my SQL statement?"

This is a common question and many times it could be a security problem because of SQL injection. Single quote is the SQL string delimiter so you may have to avoid it from you sql code or double it in order to have a valid sql statement.

There is a more elegant way to solve this problem: the use of parameters.

lets say that cnx is a valid connection the syntax in c# for .net will be :

OleDbDataAdapter daRes = new OleDbDataAdapter("SELECT IDT_EMP,EMP_NAME FROM EMP where IDT_EMP = ?",cnx);
daRes.SelectCommand.Parameters.Add(new System.Data.OleDb.OleDbParameter("IDT_EMP", System.Data.OleDb.OleDbType.VarChar, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(4)), ((System.Byte)(0)), "IDT_EMP", System.Data.DataRowVersion.Current, null));


notice :If you have many parameters all of them will be represent by a "?". You will have to follow the order of the ?s in the SQLstatement when you add your parameters to the oledbcommand parameters collection .

that's all folks

hope this helps !

comments are welcome.
 
Comments:
I'm not sure if this will make sense; but I will try and be brief. Here is a sample of SQL code generated from Toad for Oracle. I am using this because I am not having any luck using Excel's SQL inside MS Query. I keep getting the ORA-00936 missing expression error message.
So, I get the SQL to run perfect in Toad for Oracle and I just can't pinpoint what is being written wrong for it to work in Excel.

IN EXCEL it looks something like this:
[code]
SELECT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, A_COMPL_SUMMARY.PART_SEQUENCE, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.NAME, A_COMPL_SUMMARY.MONTH, A_COMPL_SUMMARY.PRODUCT_FAMILY, A_COMPL_SUMMARY.COMPLAINT, A_COMPL_SUMMARY.REPORTABLE, A_COMPL_SUMMARY.PRODUCT_RETURNED, A_COMPL_SUMMARY.CASE_DESC, A_COMPL_SUMMARY.FAILURE_INVEST_DESC, A_COMPL_SUMMARY.LHR_SEARCH, A_COMPL_SUMMARY.ROOT_CAUSE, A_COMPL_SUMMARY.CORRECTIVE_ACTION, A_COMPL_SUMMARY.REGION, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION, RP_QA_PATIENT_CODES.PATIENT_CODE, RP_QA_PATIENT_CODES.PATIENT_CODE_CLARIF, RP_QA_PATIENT_CODES.PATIENT_CODE_SEVERITY, RP_QA_PATIENT_CODES.DESCRIPTION
FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.RP_QA_PATIENT_CODES RP_QA_PATIENT_CODES, CHSUSER.RP_QA_REPORTED_DEVICE_CODES RP_QA_REPORTED_DEVICE_CODES
WHERE A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_PATIENT_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_PATIENT_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_PATIENT_CODES.CASE_NUMBER AND RP_QA_PATIENT_CODES.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND RP_QA_PATIENT_CODES.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND RP_QA_PATIENT_CODES.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND ((A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?’’PF1 Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?’’PF2 Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?’’PF3 Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?’’PF4 Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?’’PF5) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE Not Like ?) AND (A_COMPL_SUMMARY.PRODUCT_DIVISION='CP') AND (A_COMPL_SUMMARY.ENTRY_DATE>=? And A_COMPL_SUMMARY.ENTRY_DATE<=?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ?) AND (A_COMPL_SUMMARY.REGION Like ?) AND (A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ?) AND (A_COMPL_SUMMARY.COMPLAINT Like ?))
[/code]

IN TOAD FOR ORACLE It looks like this:

[code]
SELECT a_compl_summary.incident_number, a_compl_summary.case_number,
a_compl_summary.part_sequence, a_compl_summary.part_number,
a_compl_summary.lot_number, a_compl_summary.alert_date,
a_compl_summary.entry_date, a_compl_summary.NAME,
a_compl_summary.MONTH, a_compl_summary.product_family,
a_compl_summary.complaint, a_compl_summary.reportable,
a_compl_summary.product_returned, a_compl_summary.case_desc,
a_compl_summary.failure_invest_desc, a_compl_summary.lhr_search,
a_compl_summary.root_cause, a_compl_summary.corrective_action,
a_compl_summary.region,
rp_qa_reported_device_codes.reported_device_code,
rp_qa_reported_device_codes.reported_dev_clarification,
rp_qa_reported_device_codes.reported_dev_code_desc,
rp_qa_patient_codes.patient_code,
rp_qa_patient_codes.patient_code_clarif,
rp_qa_patient_codes.patient_code_severity,
rp_qa_patient_codes.description
FROM chsuser.a_compl_summary,
chsuser.rp_qa_patient_codes,
chsuser.rp_qa_reported_device_codes
WHERE ( (a_compl_summary.product_division = 'CP')
AND ( a_compl_summary.entry_date >= :date1
AND a_compl_summary.entry_date <= :date2
)
AND ( a_compl_summary.product_family LIKE :pf1
OR a_compl_summary.product_family LIKE :pf2
OR a_compl_summary.product_family LIKE :pf3
OR a_compl_summary.product_family LIKE :pf4
OR a_compl_summary.product_family LIKE :pf5
)
AND (a_compl_summary.region = :r1)
AND ( a_compl_summary.NAME = :c1
OR a_compl_summary.NAME = :c2
OR a_compl_summary.NAME = :c3
OR a_compl_summary.NAME = :c4
OR a_compl_summary.NAME = :c5
)
AND (a_compl_summary.complaint = :yorn)
AND ( rp_qa_reported_device_codes.reported_dev_clarification LIKE
:cl1
OR rp_qa_reported_device_codes.reported_dev_clarification LIKE
:cl2
OR rp_qa_reported_device_codes.reported_dev_clarification LIKE
:cl3
OR rp_qa_reported_device_codes.reported_dev_clarification LIKE
:cl4
OR rp_qa_reported_device_codes.reported_dev_clarification LIKE
:cl5
)
AND (rp_qa_reported_device_codes.reported_dev_clarification NOT LIKE
:dc1
)
AND (a_compl_summary.incident_number =
rp_qa_patient_codes.incident_number
)
AND (a_compl_summary.case_number = rp_qa_patient_codes.case_number)
AND (a_compl_summary.part_sequence = rp_qa_patient_codes.part_sequence
)
AND (a_compl_summary.incident_number =
rp_qa_reported_device_codes.incident_number
)
AND (a_compl_summary.case_number =
rp_qa_reported_device_codes.case_number
)
AND (a_compl_summary.part_sequence =
rp_qa_reported_device_codes.part_sequence
)
AND (rp_qa_reported_device_codes.incident_number =
rp_qa_patient_codes.incident_number
)
AND (rp_qa_reported_device_codes.case_number =
rp_qa_patient_codes.case_number
)
AND (rp_qa_reported_device_codes.part_sequence =
rp_qa_patient_codes.part_sequence
)
)
[/code]
 
Sorry, made a mistake. On the code that is generated from MS Excel's Query tool:

[code]
SELECT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, A_COMPL_SUMMARY.PART_SEQUENCE, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.NAME, A_COMPL_SUMMARY.MONTH, A_COMPL_SUMMARY.PRODUCT_FAMILY, A_COMPL_SUMMARY.COMPLAINT, A_COMPL_SUMMARY.REPORTABLE, A_COMPL_SUMMARY.PRODUCT_RETURNED, A_COMPL_SUMMARY.CASE_DESC, A_COMPL_SUMMARY.FAILURE_INVEST_DESC, A_COMPL_SUMMARY.LHR_SEARCH, A_COMPL_SUMMARY.ROOT_CAUSE, A_COMPL_SUMMARY.CORRECTIVE_ACTION, A_COMPL_SUMMARY.REGION, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION, RP_QA_PATIENT_CODES.PATIENT_CODE, RP_QA_PATIENT_CODES.PATIENT_CODE_CLARIF, RP_QA_PATIENT_CODES.PATIENT_CODE_SEVERITY, RP_QA_PATIENT_CODES.DESCRIPTION
FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.RP_QA_PATIENT_CODES RP_QA_PATIENT_CODES, CHSUSER.RP_QA_REPORTED_DEVICE_CODES RP_QA_REPORTED_DEVICE_CODES
WHERE A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_PATIENT_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_PATIENT_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_PATIENT_CODES.CASE_NUMBER AND RP_QA_PATIENT_CODES.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND RP_QA_PATIENT_CODES.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND RP_QA_PATIENT_CODES.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND ((A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE Not Like ?) AND (A_COMPL_SUMMARY.PRODUCT_DIVISION='CP') AND (A_COMPL_SUMMARY.ENTRY_DATE>=? And A_COMPL_SUMMARY.ENTRY_DATE<=?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ?) AND (A_COMPL_SUMMARY.REGION Like ?) AND (A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ?) AND (A_COMPL_SUMMARY.COMPLAINT Like ?))
[/code]
 
Post a Comment

Links to this post:

Create a Link



<< Home
You are a developer and work with Oracle and Microsoft technologies? Have a look!
ATOM
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

Oracle sequences and ADO .Net.
Back to work
Code generation and self description
links
Oracle data dictionary
Code generation with .net
Change all querytable in an Excel File
My Wiki contributions
WIKIWIKI
Connect a .Net application to an Oracle Database 1...
ARCHIVES
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /


Powered by Blogger

mboizeau.free.fr