From e56c0ef562c83f2a2fdffa9e6e49dd32fe36a0eb Mon Sep 17 00:00:00 2001
From: Roy Ben Shabat <Roy.mail.net@gmail.com>
Date: Tue, 4 Aug 2020 15:20:04 +0300
Subject: More restrictions on remote SQL statements.

---
 .../Tango.FSE.UI/SQL/DefaultRemoteSqlProvider.cs   | 29 ++++++++++++++++++++--
 1 file changed, 27 insertions(+), 2 deletions(-)

(limited to 'Software/Visual_Studio/FSE/Tango.FSE.UI/SQL')

diff --git a/Software/Visual_Studio/FSE/Tango.FSE.UI/SQL/DefaultRemoteSqlProvider.cs b/Software/Visual_Studio/FSE/Tango.FSE.UI/SQL/DefaultRemoteSqlProvider.cs
index 7477dc7f4..feac992b3 100644
--- a/Software/Visual_Studio/FSE/Tango.FSE.UI/SQL/DefaultRemoteSqlProvider.cs
+++ b/Software/Visual_Studio/FSE/Tango.FSE.UI/SQL/DefaultRemoteSqlProvider.cs
@@ -5,9 +5,11 @@ using System.Linq;
 using System.Text;
 using System.Threading.Tasks;
 using Tango.BL;
+using Tango.BL.Entities;
 using Tango.Core;
 using Tango.Core.DI;
 using Tango.Core.ExtensionMethods;
+using Tango.FSE.BL;
 using Tango.FSE.Common.Connection;
 using Tango.FSE.Common.SQL;
 using Tango.PPC.Shared.SQL;
@@ -96,11 +98,15 @@ namespace Tango.FSE.UI.SQL
         [TangoInject]
         private IMachineProvider MachineProvider { get; set; }
 
+        [TangoInject]
+        private FSEServicesContainer Services { get; set; }
+
         public async Task<RemoteSqlCommandResult> ExecuteSqlCommandAsync(RemoteSqlCommand command)
         {
             if (command.Mode == RemoteSqlCommandMode.Global || command.Mode == RemoteSqlCommandMode.Both)
             {
-                ValidateSqlStatement(command.SQL);
+                var machines = await Services.MachinesService.GetAllMachines();
+                ValidateSqlStatement(command.SQL, machines);
             }
 
             LogManager.Log($"Executing remote SQL command:\n{command.ToJsonString()}");
@@ -111,6 +117,11 @@ namespace Tango.FSE.UI.SQL
             {
                 LogManager.Log("Executing remote SQL command against the remote machine database.");
 
+                if (!MachineProvider.IsPPCAvailable)
+                {
+                    throw new InvalidOperationException("Could not execute the remote SQL command on the local machine's database.\nNo machine connected or connection type is not supported.");
+                }
+
                 try
                 {
                     var response = await MachineProvider.MachineOperator.SendGenericRequest<ExecuteSqlRequest, ExecuteSqlResponse>(new ExecuteSqlRequest()
@@ -126,6 +137,7 @@ namespace Tango.FSE.UI.SQL
                     LogManager.Log(ex, "Remote SQL command local execution failed.");
                     result.HasLocalError = true;
                     result.LocalError = ex.FlattenMessage();
+                    return result;
                 }
             }
 
@@ -164,7 +176,7 @@ namespace Tango.FSE.UI.SQL
             return result;
         }
 
-        private void ValidateSqlStatement(String sql)
+        private void ValidateSqlStatement(String sql, List<Machine> machines)
         {
             sql = sql.Trim().ToUpper();
 
@@ -183,6 +195,19 @@ namespace Tango.FSE.UI.SQL
                     throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements cannot be used on table '{table}' when executing against the global database.");
                 }
             }
+
+            if (sql.Contains("INSERT") || sql.Contains("UPDATE"))
+            {
+                if (!sql.Contains(MachineProvider.Machine.SerialNumber.ToUpper()))
+                {
+                    throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements must contain the connected machine's serial number, when executing against the global database.");
+                }
+
+                if (machines.Where(x => x.SerialNumber != MachineProvider.Machine.SerialNumber).Any(x => sql.Contains(x.SerialNumber.ToUpper())))
+                {
+                    throw new InvalidOperationException($"SQL command containing INSERT or UPDATE statements cannot contain a serial number other than the connected machines' serial number, when executing against the global database");
+                }
+            }
         }
 
         public RemoteSqlCommandResult ExecuteSqlCommand(RemoteSqlCommand command)
-- 
cgit v1.3.1