Re: ADO exception with character combination inside string...

On Thu, 21 Sep 2006 12:16:21 -0500, "Kevin Frevert"
<kevin@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:

"Bo Berglund" <bo.berglund@xxxxxxxxx> wrote in message
news:8ch5h2h6k6hel702grsadrd62u4qqrgv3d@xxxxxxxxxx
On 21 Sep 2006 07:28:33 -0700, "Bill Todd" <no@xxxxxx> wrote:

Enclose TNC:\CHARLIE in single quotes. Any database that follows the
SQL standard will assume that a string enclosed in double quotes is a
quoted identifier (table name, field name, etc.) not a string literal.

The string being sent as SQL is composed like this:
SQL := 'UPDATE Categories SET [Description] = ''' + edData.Text + '''
WHERE CategoryID = 4';

Just curious, is there a reason why you are not using parameters? Are there
safeguards protecting the application from SQL injection?


We don't know for what we would like to use parameters at all....

For performance and isolation reasons we have put all of our logic
into stored procedures inside the MSSQL database. This means that the
applications (there are several) that operate on the database always
use calls to the stored procedures in order to update data or retrieve
recordsets from it.
We only send the parameters in like this simplified example:

SQL := 'EXEC UpdateOrderByID @OrderID=5123, @Name=''Ericsson 1652K'',
@Description=''My description string''';
qryADO.Execute(SQL);

All of the needed actions inside the database (might be multiple
dependencies that need update in the real case) will be controlled by
the extremely well tested stored procedure. In case there are
cross-table data with various kinds of joins, these too are controlled
by the stored procedures. So there is no way a mistake by a programmer
will completely trash the database contents.
And we gain the speed advantage of running the logic inside MSSQL too.

But of course things like the order description are user entered data
and they might well enter a file path (as in the case we got) as a
part of some description. We were very surprised that the SQL string
was not directly passed to the database.
After all we have been working with ADO and MSSQL since about 6 years
by now (switched from MS Access)....
Only goes to show that you never know everything about anything.

/Bo

.

Relevant Pages

  • Re: return multiple rows from sql statement
    ... Concatening SQL ... strings from input values is almost certainly a safe path to SQL ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)
  • Re: Serious errors with Create view command
    ... the database is opened? ... There's no database on earth that behaves that way! ... What on EARTH does the option of 'Edit Stored procedures' do? ... I can't paste it INTO the view designer sql view ...
    (microsoft.public.fox.helpwanted)
  • Re: Establish connection with and transferring data to Access
    ... If you Google for SQL +"Insert into" you'll a dozen tutorials. ... The specific problem with your code is that you need to quote string ... Dim vConnection As New ADODB.Connection ... Doug's code opens and writes to an existing database. ...
    (microsoft.public.word.vba.general)
  • Re: Transaction Oriented Architecture (TOA)
    ... OOP is not required to wrap SQL. ... of stored procedures to act as an API for application programmers ... writing code against the database. ... With a well-defined API in place, ...
    (comp.object)
  • Re: Random Access Files in databases
    ... I am not a Database design guru either, but it might be wise to break ... location and read in in the string, and then you want to get an integer, so ... I'm pretty sure the SQL language does have ways to seek around in BLOB ... some SQL purist are quick to point out that MySQL doesn't implement the SQL ...
    (comp.lang.java.programmer)
Loading