Unzipping Word Documents in T-SQL (2024-06-19)

SQLServerCentral Newsletter for June 19, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Unzipping Word Documents in T-SQL]( - [SQL Server TRY CATCH, RAISERROR and THROW for Error Handling]( - [From the SQL Server Central Blogs - Export Extended Event Results to CSV or Table]( - [From the SQL Server Central Blogs - Planview Portfolios REST API with ADF – Retrieving Actual Data]( - [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Question of the Day - [Dropping a Logon Trigger]( The Voice of the DBA  SQL Injection Is Not Acceptable SQL Injection has been a problem for my entire career. Thirty years ago I could have easily just blamed this on ignorance, as most of our developers didn't think about the nefarious ways that hackers enter data in our applications. These days, there isn't a good reason for this to keep happening, and the problem is us. I think that we don't provide good examples or training on secure coding or secure architecture as a normal part of teaching programming. In many organizations, we don't check for issues and prevent their release. Some do, but many don't. On top of this, the existing code is usually a poor template for writing future code. I do think Microsoft aims for secure coding in SQL Server but in Windows, there is work to be done there. A few months ago, I saw [an article]( that noted the US CISA organization and the FBI issued [a secure-by-design alert]( (PDF) that noted there is no excuse for SQL Injection vulnerabilities (SQLi) in modern software. This alert notes that SQLi has been an "[unforgivable vulnerability]( since at least 2007. Inside the document on vulnerabilities, it notes that a single quote can't be used in certain fields: username, password, ID field, or numeric field. They also note that co-mingling user data and query data, like constructing queries on demand, is a poor practice. The alert even emphasizes that developers are engaging in poor practices when they "fail to treat user-supplied content as potentially malicious." I agree, and their recommendations are worth reading and implementing. If your boss doesn't want to spend time on these, point out the bulletin and note that since this is a published advisory, I wouldn't be surprised to start seeing lawsuits in the US or even insurance claim denials if your software team doesn't follow these practices. Note that the list includes leadership support of secure coding and secure design principles. I doubt this has changed a lot, but I think some managers likely see this as a) a good idea, and b) a way to mitigate potential issues down the road. Changing the habits of software developers, updating code snippets or patterns, and even adding linting/static-code-analysis to CI pipelines take time, as does the training for developers. However, it's something that has to start changing over time to get better at building higher quality, more secure software. I'd like to see insurance companies refuse to indemnify or cover losses or problems from software that is written from this point forward and is vulnerable to SQL Injection. There are far too many tools out, and software is too critical to allow these types of simple coding errors to proliferate. I'd also be pressuring companies to ensure older code is being actively refactored to reduce the number of vulnerabilities over time for all software they still support. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Unzipping Word Documents in T-SQL]( cmartel 20772 from SQLServerCentral Learn how you can use T-SQL to uncompress Word files. [External Article]( [SQL Server TRY CATCH, RAISERROR and THROW for Error Handling]( Additional Articles from MSSQLTips.com Learn about SQL Server error handling using TRY CATCH, RAISERROR, and THROW for stored procedures, triggers, and user-defined functions. From the SQL Server Central Blogs - [Export Extended Event Results to CSV or Table]( david.fowler 42596 from SQL Undercover This is something that I’ve seen pop up a couple of times on various forums recently, how can we export the results of an XEvent session. So I thought... [Blog Post]( From the SQL Server Central Blogs - [Planview Portfolios REST API with ADF – Retrieving Actual Data]( Koen Verbeeck from Koen Verbeeck In the introduction of this blog post series, I explained the use case: extracting data from the Planview Portfolios REST API using Azure Data Factory. Any tool that can... [SQL Server 2022 Revealed]( [SQL Server 2022 Revealed: A Hybrid Data Platform Powered by Security, Performance, and Availability]( Additional Articles from SQLServerCentral Know how to use the new capabilities and cloud integrations in SQL Server 2022. This book covers the many innovative integrations with the Azure Cloud that make SQL Server 2022 the most cloud-connected edition ever. The book covers cutting-edge features such as the blockchain-based Ledger for creating a tamper-evident record of changes to data over time that you can rely on to be correct and reliable.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Dropping a Logon Trigger I created this trigger for testing on SQL Server 2022: CREATE TRIGGER checksteve ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= N'ARISTOTLE\Steve' PRINT 'Steve logged in' END; GO Now I want to drop it. What do I run? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) A Lack of Memory Grant Feedback Persistence SQL Server has added an Intelligent Query Processing feature called Memory Grant Feedback. In SQL Server 2022, this can be persisted across queries to improve the performance of future executions. In SQL Server 2022, when is memory grant feedback not persisted if the plan is evicted from cache? (choose 2) Answer: When query store is not enabled, When the query store is not in read write mode Explanation: Feedback is not persisted when the query store is not enabled or when it is not in read write mode. Ref: Memory Grant Feedback Persistence - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Canot connect to Prod1]( - Tried using SQL authentication but getting below error. A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (Provider : TCP Provider, error: 9 - The specified network name is no longer available) Microsoft SQL server, Error: 64) [Cabot connect to Prod1]( - Tried using both Windows & SQL authentication but getting same error. A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (Provider : TCP Provider, error: 9 - The specified network name is no longer available) Microsoft SQL server, Error: 64) SQL Server 2016 - Development and T-SQL [Flexable/Effective means to join the Nth record]( - I have no doubt this has been covered many times and has many answers but I'm hoping by now this has been refined down to where you can get not just teh 1st or last record but the Nth record. Let say I has a join like the below and I want just teh first […] Development - SQL Server 2014 [“user_scans” of table “sys.dm_db_index_usage_stats”]( - I am trying to find answer to the below. What exactly the scan means for the field "user_scans" being with the table "sys.dm_db_index_usage_stats" ? Like, there are tables in our prod which aren't used for sure as the module is shutdown 4 years back but the DB holds all the tables related to that module. […] SQL Server 2019 - Administration [SQL SSRS license info]( - hello,  i am trying to find where SSRS edition and product key information are stored in SSRS 2019 and above version. until 2016 i was ablet to pull information from registry for some reason it is not available from 2019 and above versions.  HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\($instance)\SSRS\Reporting Services\ReportServer SQL Server 2019 - Development [Is Solution PIVOT?]( - Would appreciate some help. I'm trying to convert the 2-column list which is 2 columns to a flat record for each "MyId" ... the header names on the flat record are for example purposes and not important. I'm going to insert this into a table. The example has a max of 3 PayeeId for any […] [sp_whoisactive filtering]( - I am experiencing difficulty filtering the results in the sp_whoisactive stored procedure when attempting to apply multiple filters. Can someone please provide assistance or guidance? [Strange behavior passing a variable in XML.value]( - Hi, anyone would know why as soon as I add a WHERE statement (even WHERE 1 = 1) I would get an error? Declare @Language nvarchar(10) = 'en' DROP TABLE IF EXISTS #Temp CREATE TABLE #Temp (TestName varchar(100),TestDescription varchar(max)) INSERT INTO #Temp values ('Test1','English DescriptionFrench Description') SELECT TestName,     CAST(TestDescription […] General [Have you seen this in the news today?]( - You might want to look into this issue: Windows flaw lets hackers sneak into your PC over Wi-Fi | Fox News I don't know how serious this might be, but maybe should be checked out. SQL Server 2022 - Administration [how to find the cpu memory disk and network of the OS where RDS is residing.]( - hi, i have one RDS, but i do not have any other permissions, and i need to know the cpu , memeory disk and network staus of the OS where RDS is residing. if i had sqlserver on my machine it was so easy for me to press alt ctrl del to find the status […] [Contained Availability Groups and Replication]( - Hi, I have a couple of questions around contained AOAG: Is it still the case that you cannot replicate a database within a contained AOAG? Are you able to replicate to the nodes involved in the cluster outside of the contained AOAG? TIA SQL Server 2022 - Development [how to assign studentid from one table to other one? please]( - Good Morning,  how to assign query one results to query 2 student id, each record should have each value, rather assigning one to all 20k students. please advise. i tried one but it is assigned same id to all STG_Student. please  -- Query 1 SELECT STATESTUDENTID FROM DEV_SLDS.LEGACY.STUDENTIDPOOL WHERE IDSTATUS = 'UNASSIGNED' AND […] [How to run custom python module on SQL Server?]( - I am working on a small test script on SQL server 2022 using python module. The module does the simple calculation of pi * 2 (3.1415926 * 2). The test script does not work with MouleNotFoundError when using the custom module. While the test script worked fine when same python code was directly imbedded in […] [Upgrade to SQL2019 from 2012 Query is taking time, Need Query Optimization steps]( - Upgrade to SQL2019 from 2012 Query is taking time, Need Query Optimization steps and Pre requisites to optimize Query Performance and CPU Usage [What happened to my SSMS shortcuts in 20.1?]( - I installed SSMS 20.1 and all my trusted keyboard shortcuts don't work anymore... E --> Execute R --> Toggle result pane It seems to always wait for the second character in the sequence... I tried to reset keyboard settings in tools/options but no change Is my install broken?    [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -