sql - 试图更新 SQL 数据，但数据包含有很多 ' 所以我得到错误

Question

我正在尝试更新 SQL 数据，但数据包含 javascript 和很多'所以我得到错误。

SQL 语句如下所示：

UPDATE SystemConfiguration
SET    HeaderScript = '<script> (function(w,d,s,l,i){w[l]=w[l]||[];w[l].push({'gtm.start': new Date().getTime(),event:'gtm.js'});var f=d.getElementsByTagName(s)[0], j=d.createElement(s),dl=l!='dataLayer'?'&l='+l:'';j.async=true;j.src= '//www.googletagmanager.com/gtm.js?id='+i+dl;f.parentNode.insertBefore(j,f); })(window,document,'script','dataLayer','GTM-P5672Q'); </script> <script type="text/javascript"> function OnContactSelected(source, eventArgs) { var hdnValueID = "<%= hdnValue.ClientID %>"; document.getElementById(hdnValueID).value = eventArgs.get_value(); __doPostBack(hdnValueID, ""); } </script>  <script> (function () { var _fbq = window._fbq || (window._fbq = []); if (!_fbq.loaded) { var fbds = document.createElement('script'); fbds.async = true; fbds.src = '//connect.facebook.net/en_US/fbds.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(fbds, s); _fbq.loaded = true; } _fbq.push(['addPixelId', '573001079512539']); })(); window._fbq = window._fbq || []; window._fbq.push(['track', 'PixelInitialized', {}]); </script> <noscript> <img height="1" width="1" alt="" style="display: none" src="https://www.facebook.com/tr?id=573001079512539&ev=PixelInitialized" /> </noscript> <!-- Facebook Pixel Code --> <script> !function(f,b,e,v,n,t,s){if(f.fbq)return;n=f.fbq=function(){n.callMethod? n.callMethod.apply(n,arguments):n.queue.push(arguments)};if(!f._fbq)f._fbq=n; n.push=n;n.loaded=!0;n.version='2.0';n.queue=[];t=b.createElement(e);t.async=!0; t.src=v;s=b.getElementsByTagName(e)[0];s.parentNode.insertBefore(t,s)}(window, document,'script','https://connect.facebook.net/en_US/fbevents.js'); fbq('init', '573001079512539'); fbq('track', "PageView");</script> <noscript><img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=573001079512539&ev=PageView&noscript=1" /></noscript> <!-- End Facebook Pixel Code -->'
WHERE  ID = 1

有什么解决办法吗？谢谢

标签： sqlsql-server

Answer 1

使用参数：

UPDATE SystemConfiguration
SET    HeaderScript = @script
WHERE  ID = 1

或使用 '' 正确转义 '：

declare @script nvarchar(max) = N'<script> (function(w,d,s,l,i){w[l]=w[l]||[];w[l].push({''gtm.start'': new Date().getTime(),event:''gtm.js''});var f=d.getElementsByTagName(s)[0], j=d.createElement(s),dl=l!=''dataLayer''?''&l=''+l:'''';j.async=true;j.src= ''//www.googletagmanager.com/gtm.js?id=''+i+dl;f.parentNode.insertBefore(j,f); })(window,document,''script'',''dataLayer'',''GTM-P5672Q''); </script> <script type="text/javascript"> function OnContactSelected(source, eventArgs) { var hdnValueID = "<%= hdnValue.ClientID %>"; document.getElementById(hdnValueID).value = eventArgs.get_value(); __doPostBack(hdnValueID, ""); } </script>  <script> (function () { var _fbq = window._fbq || (window._fbq = []); if (!_fbq.loaded) { var fbds = document.createElement(''script''); fbds.async = true; fbds.src = ''//connect.facebook.net/en_US/fbds.js''; var s = document.getElementsByTagName(''script'')[0]; s.parentNode.insertBefore(fbds, s); _fbq.loaded = true; } _fbq.push([''addPixelId'', ''573001079512539'']); })(); window._fbq = window._fbq || []; window._fbq.push([''track'', ''PixelInitialized'', {}]); </script> <noscript> <img height="1" width="1" alt="" style="display: none" src="https://www.facebook.com/tr?id=573001079512539&ev=PixelInitialized" /> </noscript> <!-- Facebook Pixel Code --> <script> !function(f,b,e,v,n,t,s){if(f.fbq)return;n=f.fbq=function(){n.callMethod? n.callMethod.apply(n,arguments):n.queue.push(arguments)};if(!f._fbq)f._fbq=n; n.push=n;n.loaded=!0;n.version=''2.0'';n.queue=[];t=b.createElement(e);t.async=!0; t.src=v;s=b.getElementsByTagName(e)[0];s.parentNode.insertBefore(t,s)}(window, document,''script'',''https://connect.facebook.net/en_US/fbevents.js''); fbq(''init'', ''573001079512539''); fbq(''track'', "PageView");</script> <noscript><img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=573001079512539&ev=PageView&noscript=1" /></noscript> <!-- End Facebook Pixel Code -->'

在 SSMS 中，您可以用 '' 搜索替换 '。