TEXT...ENDTEXT is a door to SQL Injection Attacks as much as anything else we put inside the ODBC tube.
LOCAL SQLStmt AS String
** Thisform.txtSearch.Value = "1 = 1; DROP TABLE Customers;"
TEXT TO m.SQLStmt NOSHOW TEXTMERGE SELECT * FROM Customers<<IIF(!EMPTY(Thisform.txtSearch.Value), " WHERE Name LIKE ?('%' + Thisform.txtSearch.Value + '%')", "")>>; ENDTEXT
** or
TEXT TO m.SQLStmt NOSHOW TEXTMERGE SELECT * FROM Customers<<IIF(!EMPTY(Thisform.txtSearch.Value), " WHERE Name = ?Thisform.txtSearch.Value", "")>>; ENDTEXT
Unless there is at least one customer with a name that starts or contains "1 = 1; DROP TABLE Customers;", the resulting cursor will be empty and the Customers table perfectly usable afterwards. Otherwise, the cursor will return the "1 = 1; DROP TABLE Customers;" customers. And the Customers table will still be there.
On Fri, Jun 28, 2019 at 3:27 PM Stephen Russell srussell705@gmail.com wrote:
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta...
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]