Apostraphe value in sql

[ntnewbies]

hi bruce,
when i filter a browse with a value, say King's Speech, i found that the filter fails.
The value inside the sql database is indeed King's Speech.
If i use jsok, then nettalk would convert it as King's Speech. Again the filter will fail.
For now, what i did is i check the value of filter. If it contains ', then i will replace it with '' using string theory.
then the filter works.

may i know if there is any nettalk method or a setting to handle the apostraphe? thanks.

regards,
jason
nt14.20
c11.1

[seanh]

a single '  may have problem, but I would not have thought so in a filter context.
Have you tried doubling up ie  King''s Speech   That's 2 ' not 1 "

[ntnewbies]

Hi Sean,
Yes, that's what i did. Use the string theory to detect single ' and made it double '' before passing it to the filter.
What i want to know if there is a nettalk class that can does that for us. p_web.jsok converts single ' but to a different value.

regards,
Jason

[Bruce]

reproduce in an example app, and post that here.

[ntnewbies]

hi bruce,

you want me to produce example in sql ? because i am trying to highlight about sql here.

Let me elaborate:

Let's say Product Name is King's Kong
I want to search for all the invoice with that value. I will issue the sql query in my embed code

InvoiceItems{Prop:Sql} = 'Select * from dbo.InvoiceItems where ProductName = ''' & p_web.GSV('SelectedProductName') & ''''

the above query will fail because apostraphe in the Product Name because in sql query, my code produce Select * from dbo.InvoiceItems where ProductName = 'King's Kong'    which is wrong.
i need to do this:

Select * from dbo.InvoiceItems where ProductName = 'King''s Kong'


Right now, i can rectify the issue using string theory.

What i am asking is if there is a nettalk method to convert single apostraphe to double apostraphe then it would be useful.

regards,
Jason

[Bruce]

Hi Jason,

>> you want me to produce example in sql?

sure.

>> I will issue the sql query in my embed code
>> InvoiceItems{Prop:Sql} = 'Select * from dbo.InvoiceItems where ProductName = ''' & p_web.GSV('SelectedProductName') & ''''

See - already you are exposing detail which you haven't done before. That's the purpose of an example.

You should not be using Prop:SQL at all in your program. This is very, very bad. Using Prop:Sql will open up your program to SQL injection attacks.
Do not do it.

Equally, you don't need to be using Prop:Sql. The API's support VIEWS as a return structure, and VIEW's support filters. You should be using that.

Let me say it again - if you are using Prop:Sql in General, and with user entered data in Particular, then your web app is doomed to failure. If you do go this route please let me know the URL of your service so I can delight in dropping all your tables from the database, randomly filling your database with Spam, altering all the unit prices so I can get stuff for free, and having fun in all other kinds of ways.

>> What i am asking is if there is a nettalk method to convert single apostrophe to double apostrophe then it would be useful.

Clarion has a command QUOTE which does that.

Cheers
Bruce

[osquiabro]

"You should not be using Prop:SQL at all in your program. This is very, very bad. Using Prop:Sql will open up your program to SQL injection attacks. Do not do it."

Bruce how is possible SQL injection attacks with prop:sql ?

i can create a public demo that use prop:sql  for you to try the sql injetion

[Bruce]

>> Bruce how is possible SQL injection attacks with prop:sql ?

Prop:Sql passes the SQL you write straight through to the database for execution.

By contrast the drivers create "Parameterized Queries" - which separate the "code" part of the sql from the data part of the sql. Separating the code and the data means that code is code and data is data.

With prop:Sql the code and data are smooshed together. So Jason's line;

InvoiceItems{Prop:Sql} = 'Select * from dbo.InvoiceItems where ProductName = ''' & p_web.GSV('SelectedProductName') & ''''

Is vulnerable to manipulation of SelectedProductName.
For example, say this is coming from an entry field on the window. Then I enter;

' ; Drop Table Customers; '

This is a trivial example, but shows the root problem. Once a user can enter "any sql", well they can do anything they like to your database, and none of it is good.