protected void Page_Load(object sender, EventArgs e) { // Generic response package /////////////////////////////////// Dictionary<String, Object> responsePackage = new Dictionary<String, Object>(); List<Dictionary<String, String>> responses = new List<Dictionary<String, String>>(); Dictionary<String, String> response = new Dictionary<String, String>(); Dictionary<String, String> status = new Dictionary<String, String>(); /////////////////////////////////////////////////////////////// JavaScriptSerializer rs = new JavaScriptSerializer(); try { /* Response.Write("Hello"); return; Response.Write(System.Environment.GetEnvironmentVariable("PPL.Env")); return; */ String credentialUID = Request.QueryString["u"]; String credentialPSWD = Request.QueryString["p"]; String credentialEPSWD = null; // Comment out the following line for production. credentialEPSWD = Request.QueryString["ep"]; if (credentialPSWD != null) { Security security = new Security(); Response.Write(security.encrypt(credentialUID, credentialPSWD)); return; } else if (credentialEPSWD != null) { Security security = new Security(); Response.Write(security.decrypt(credentialUID, credentialEPSWD)); return; } Dictionary<String, Object> jsonRequest = new Dictionary<String, Object>(); //JavaScriptSerializer rs = new JavaScriptSerializer(); try { String[] theFormRequest = Request.Form.GetValues(0); jsonRequest = rs.Deserialize<Dictionary<String, Object>>(theFormRequest[0]); } catch (Exception tmpE) { Stream instream = Page.Request.InputStream; BinaryReader br = new BinaryReader(instream, System.Text.Encoding.UTF8); byte[] bytes = br.ReadBytes((int)instream.Length); System.Text.Encoding enc = System.Text.Encoding.ASCII; jsonRequest = rs.Deserialize<Dictionary<String, Object>>(enc.GetString(bytes)); } String request = (String)jsonRequest["request"]; String action = (String)jsonRequest["action"]; //String connectionType = (String)jsonRequest["connectionType"]; Dictionary<String, Object> requestData = (Dictionary<String, Object>)jsonRequest["requestData"]; DataConnection theDB = new DataConnection(); String userName = Request.ServerVariables["AUTH_USER"]; //Security securityA = new Security(); //Boolean ok = securityA.CheckGroup(new String[] {"PPL\\ISD-DL-GIS","aaa"}); switch (action) { case "getUserId": response.Add("userid", userName.Substring(userName.LastIndexOf("\\") + 1)); //response.Add("userid", System.Web.HttpContext.Current.Request.LogonUserIdentity.Name); status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(response)); return; //break; case "checkSecurity": Security security = new Security(); if //(security.CheckGroup(((ArrayList)requestData["groups"]))) (security.CheckGroup((ArrayList)requestData["groups"])) { response.Add("success", "true"); } else { response.Add("success", "false"); } status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(response)); return; //break; } switch ((String)requestData["form:connectionType"]) { case "SOAP": Dictionary<String, String> credentials = new Dictionary<String, String>(); if(requestData.ContainsKey("form:connection")) { credentials = theDB.getSOAPCredentials((String)requestData["form:connection"]); } switch (action) { default: String soapEnv = ""; Random r = new Random(); DateTime created = DateTime.Now; string nonce = Convert.ToBase64String(Encoding.ASCII.GetBytes(SHA1Encrypt(created + r.Next().ToString()))); soapEnv += "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'>"; String securityHeader = ""; securityHeader += "<wsse:Security soapenv:mustUnderstand='1' xmlns:wsse='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd'>"; securityHeader += "<wsu:Timestamp wsu:Id='" + System.Guid.NewGuid().ToString() + "' xmlns:wsu='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd'>"; String createdDate = TimeZoneInfo.ConvertTimeToUtc(created).ToString("yyyy-MM-dd'T'HH:mm:ss.FFF'Z'"); String expirationDate = TimeZoneInfo.ConvertTimeToUtc(created).AddSeconds(30).ToString("yyyy-MM-dd'T'HH:mm:ss.FFF'Z'"); securityHeader += "<wsu:Created>" + createdDate + "</wsu:Created>"; securityHeader += "<wsu:Expires>" + expirationDate + "</wsu:Expires>"; securityHeader += "</wsu:Timestamp>"; if (credentials.ContainsKey("userid")) { securityHeader += "<wsse:UsernameToken wsu:Id='" + System.Guid.NewGuid().ToString() + "' xmlns:wsu='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd'>"; securityHeader += "<wsse:Username>" + credentials["userid"] + "</wsse:Username>"; securityHeader += "<wsse:Password Type='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText'>" + credentials["password"] + "</wsse:Password>"; securityHeader += "<wsse:Nonce EncodingType='http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary'>" + nonce + "</wsse:Nonce>"; securityHeader += "<wsu:Created>" + createdDate + "</wsu:Created>"; securityHeader += "</wsse:UsernameToken>"; } securityHeader += "</wsse:Security>"; soapEnv += "<soapenv:Header>"; soapEnv += securityHeader; soapEnv += "</soapenv:Header>"; soapEnv += "<soapenv:Body"; soapEnv += "></soapenv:Body>"; soapEnv += "</soapenv:Envelope>"; XmlDocument doc = new XmlDocument(); doc.LoadXml(soapEnv); XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable); nsmgr.AddNamespace("soapenv", "http://schemas.xmlsoap.org/soap/envelope/"); nsmgr.AddNamespace("wsse", "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"); nsmgr.AddNamespace("wsu", "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd"); String endPoint = ""; String soapAction = ""; Dictionary<String, Object> actionDetail = (Dictionary<String, Object>)requestData["form:actionDetail"]; Dictionary<String, Object> searchActionDetail = null; Dictionary<String, Object> searchRequestActionDetail = null; Dictionary<String, Object> namespaces = null; if (actionDetail.ContainsKey(action.ToLower())) { searchActionDetail = (Dictionary<String, Object>)actionDetail[action.ToLower()]; if (searchActionDetail.ContainsKey("request")) { searchRequestActionDetail = (Dictionary<String, Object>)searchActionDetail["request"]; endPoint = (String)credentials["server"]; soapAction = (String)searchRequestActionDetail["action"]; if (searchRequestActionDetail.ContainsKey("namespaces")) { namespaces = (Dictionary<String, Object>)searchRequestActionDetail["namespaces"]; foreach (var namespaceItem in namespaces) { //soapEnv += " xmlns:" + namespaceItem.Key + "=\"" + namespaceItem.Value + "\""; nsmgr.AddNamespace(namespaceItem.Key, namespaceItem.Value.ToString()); } } } } if (action == "subsearch") { requestData.Add((String)requestData["sub:childReference"], (String)requestData["sub:Key"]); //requestData.Add("key", (String)requestData["sub:Key"]); } foreach(var field in requestData) { String[] nodeParts = field.Key.Split('/'); String prePartSegment = "/soapenv:Envelope/soapenv:Body"; String postPartSegment = String.Copy(prePartSegment); XmlNode newNode = null; foreach(var part in nodeParts) { if (!part.StartsWith("form:") && !part.StartsWith("return:") && !part.StartsWith("sub:") && !part.StartsWith("key") && !part.Contains("xrefSubform")) { String[] theNamespace = part.Split(':'); postPartSegment += "/" + part; if (doc.SelectSingleNode(postPartSegment, nsmgr) == null) { if (theNamespace.Length > 1) { newNode = doc.SelectSingleNode(prePartSegment, nsmgr).AppendChild(doc.CreateElement(part, (String)namespaces[theNamespace[0]])); } else { newNode = doc.SelectSingleNode(prePartSegment, nsmgr).AppendChild(doc.CreateElement(part)); } } prePartSegment = String.Copy(postPartSegment); } } if (newNode != null) { String tmpValue = field.Value.ToString(); if (nodeParts[nodeParts.Length - 1].ToLower().Contains("date")) { newNode.AppendChild(doc.CreateTextNode(tmpValue.Substring(6, 4) + tmpValue.Substring(0, 2) + tmpValue.Substring(3,2))); } else { newNode.AppendChild(doc.CreateTextNode(tmpValue)); } } } //String soapDocumentString = doc.InnerXml; //byte[] soapRequestDocument = new byte[doc.InnerXml.Length * sizeof(char)]; //System.Buffer.BlockCopy(doc.InnerXml.ToCharArray(), 0, soapRequestDocument, 0, soapRequestDocument.Length); //System.Text.UTF8Encoding UTF8 = new System.Text.UTF8Encoding(); //Byte[] soapRequestDocument = UTF8.GetBytes(doc.InnerXml); System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding(); byte[] soapRequestDocument = encoding.GetBytes(doc.InnerXml); HttpWebRequest soapRequest = (HttpWebRequest)WebRequest.Create(endPoint); //soapRequest.Credentials = CredentialCache.DefaultCredentials; //((HttpWebRequest)soapRequest).UserAgent = ".NET Framework Example Client"; soapRequest.Method = "POST"; soapRequest.ContentLength = soapRequestDocument.Length; soapRequest.ContentType = "text/xml;charset=UTF-8"; soapRequest.Accept = "text/xml"; soapRequest.Headers.Add("SOAPAction", soapAction); Stream dataStream = soapRequest.GetRequestStream(); dataStream.Write (soapRequestDocument, 0, soapRequestDocument.Length); dataStream.Close (); //WebResponse soapResponse = null; //try //{ //soapResponse = soapRequest.GetResponse(); string responseFromServer = null; try { using (WebResponse soapResponse = soapRequest.GetResponse()) { //} //catch (WebException we) //{ /* response = new Dictionary<String, String>(); response.Add("success", "false"); response.Add("message", we.ToString()); responses.Add(response); Response.Write(rs.Serialize(responses)); return; */ //} dataStream = soapResponse.GetResponseStream(); StreamReader reader = new StreamReader(dataStream); responseFromServer = reader.ReadToEnd(); reader.Close(); dataStream.Close(); soapResponse.Close(); } } catch (WebException eee) { //responses.Add(response); //responseFromServer = responseFromServer; /* response.Add("success", "false"); doc.LoadXml(new StreamReader(eee.Response.GetResponseStream()).ReadToEnd()); response.Add("message", "A HTTP/SOAP Error has occured.\nA member of the support staff has been notified.\n" + ((doc.GetElementsByTagName("body").Count > 0) ? doc.GetElementsByTagName("body")[0].InnerText : doc.InnerXml) + "\n\n" + (new ASCIIEncoding().GetString(soapRequestDocument)) ); Response.Write(rs.Serialize(response)); */ status.Add("result", "false"); status.Add("message", "A HTTP/SOAP Error has occured.\nA member of the support staff has been notified.\n"); responsePackage.Add("data", responses); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); return; } XmlDocument responseDoc = new XmlDocument(); responseDoc.LoadXml(responseFromServer); XmlNamespaceManager responseNsmgr = new XmlNamespaceManager(doc.NameTable); String keyRoot = ""; Dictionary<String, Object> actionDetailResponse = (Dictionary<String, Object>)requestData["form:actionDetail"]; if (actionDetailResponse.ContainsKey(action.ToLower())) { Dictionary<String, Object> searchActionDetailResponse = (Dictionary<String, Object>)actionDetail[action.ToLower()]; if (searchActionDetailResponse.ContainsKey("response")) { Dictionary<String, Object> searchResponseActionDetail = (Dictionary<String, Object>)searchActionDetailResponse["response"]; if (searchResponseActionDetail.ContainsKey("key")) { // Need to do this for the database side as well. if ((searchResponseActionDetail["key"]).GetType().Name.Equals("ArrayList")) { keyRoot = (String)(((ArrayList)(searchResponseActionDetail["key"]))[0]); } else { keyRoot = (String)searchResponseActionDetail["key"]; } } else { if ((requestData["key"]).GetType().Name.Equals("ArrayList")) { keyRoot = (String)(((ArrayList)(requestData["key"]))[0]); } else { keyRoot = (String)requestData["key"]; } } keyRoot = keyRoot.Substring(0,keyRoot.LastIndexOf('/')); if (searchResponseActionDetail.ContainsKey("namespaces")) { Dictionary<String, Object> responseNamespaces = (Dictionary<String, Object>)searchResponseActionDetail["namespaces"]; foreach (var responseNamespaceItem in responseNamespaces) { //soapEnv += " xmlns:" + namespaceItem.Key + "=\"" + namespaceItem.Value + "\""; responseNsmgr.AddNamespace(responseNamespaceItem.Key, responseNamespaceItem.Value.ToString()); } } } } String documentRoot = "/" + responseDoc.DocumentElement.Name + "/" + responseDoc.DocumentElement.Name.Replace("Envelope", "Body") + "/"; String resultNodes = documentRoot + keyRoot; foreach (XmlNode node in responseDoc.DocumentElement.SelectNodes(resultNodes, responseNsmgr)) { response = new Dictionary<String, String>(); //if(node.SelectSingleNode("ns:contractor_ID", responseNsmgr).InnerText /*if (action.ToLower() == "loadselect") { String relativeNode = ((String)requestData["key"]).Replace(keyRoot + "/", ""); XmlNode fieldNode = node.SelectSingleNode(relativeNode, responseNsmgr); if (fieldNode != null) { response.Add("KEY", fieldNode.InnerText); } relativeNode = ((String)requestData["value"]).Replace(keyRoot + "/", ""); fieldNode = node.SelectSingleNode(relativeNode, responseNsmgr); if (fieldNode != null) { response.Add("VALUE", fieldNode.InnerText); } } else*/ if (requestData.ContainsKey("return:fields")) { foreach (String field in (ArrayList)requestData["return:fields"]) { String relativeNode = field.Replace(keyRoot + "/", ""); XmlNode fieldNode = node.SelectSingleNode(relativeNode, responseNsmgr); if (fieldNode != null) { if (field.ToLower().Contains("date")) { response.Add(field, fieldNode.InnerText.Substring(4, 2) + "/" + fieldNode.InnerText.Substring(6, 2) + "/" + fieldNode.InnerText.Substring(0, 4)); } else { response.Add(field, fieldNode.InnerText); } } } } else // Might not need this part. { foreach (KeyValuePair<String, Object> field in requestData) { try { String relativeNode = field.Key.Replace(keyRoot + "/", ""); XmlNode fieldNode = node.SelectSingleNode(relativeNode, responseNsmgr); if (fieldNode != null) { response.Add(field.Key, fieldNode.InnerText); } } catch (Exception ex) { } } } responses.Add(response); } //if (responses.Count > 1) if (action.ToLower() == "search" || action.ToLower() == "subsearch" || action.ToLower() == "loadselect") { // Must be a search responses.Sort(delegate(Dictionary<String, String> d1, Dictionary<String, String> d2) { try { return d1[(String)((ArrayList)requestData["return:fields"])[1]].CompareTo(d2[(String)((ArrayList)requestData["return:fields"])[1]]); } catch (Exception se) { return 0; } }); status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", responses); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(responses)); } else if (action.ToLower() == "loadselect-xxxxxxx") { responses.Sort(delegate(Dictionary<String, String> d1, Dictionary<String, String> d2) { return d1["VALUE"].CompareTo(d2["VALUE"]); }); status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", responses); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(responses)); } else { /* This is what I want to do: status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); */ if (action != "Search" || action.ToLower() == "loadselect") { //response.Add("success", "true"); //Response.Write(rs.Serialize(response)); status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); } } break; } break; case "DATABASE": if (theDB.OpenConnection((String)requestData["form:connection"])) { String sql = ""; String sql2 = ""; String sql3 = ""; String sql4 = ""; String finalSQL = ""; String theComma = ""; String theComma2 = ""; // Allow for case insensitive search. theDB.execNonQuery("alter session set NLS_SORT=BINARY_CI"); theDB.execNonQuery("alter session set NLS_COMP=LINGUISTIC"); OracleDataReader resultA = null; switch (action) { case "Email": sql = "select * from " + (String)requestData["fromEmailSource"] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "select "; sql2 = ""; theComma = ""; for (int i = 0; i < resultA.FieldCount; i++) { if ((String)requestData["emailID"] == resultA.GetName(i)) { if (resultA.GetFieldType(i) == typeof(DateTime)) { sql2 += theComma + "TO_CHAR(" + resultA.GetName(i) + ", 'MM/DD/YYYY') " + resultA.GetName(i); } else if (resultA.GetFieldType(i) != typeof(String)) { sql2 += theComma + "TO_CHAR(" + resultA.GetName(i) + ") " + resultA.GetName(i); } else { sql2 += theComma + resultA.GetName(i); } if (!sql2.Equals("")) { theComma = ", "; } } } resultA.Close(); finalSQL = sql + sql2 + " from " + (String)requestData["fromEmailSource"] + " where " + (String)requestData["fromEmailUserID"] + " = '" + (userName.Substring(userName.LastIndexOf("\\") + 1)).Replace("'", "''") + "'"; //finalSQL = "select * from (" + finalSQL + ") where ROWNUM <= 100"; OracleDataReader result = theDB.execQuery(finalSQL); String emailString = ""; MailAddress fromAddress = null; if (result.Read()) { for (int i = 0; i < result.FieldCount; i++) { try { emailString = result.GetString(i); } catch (Exception ex) { } fromAddress = new MailAddress(emailString); } } result.Close(); var message = new MailMessage(); message.From = fromAddress; message.To.Add(fromAddress); message.Subject = (String)requestData["Subject"]; message.Body = (String)requestData["Body"]; foreach (var subTable in requestData) { if (subTable.Key.IndexOf("xrefSubform") > -1) { String subField = subTable.Key.Substring(0, subTable.Key.IndexOf("xrefSubform")); // subTableInfo variable needs to be hardened against SQL injection String[] subTableInfo = ((String)subTable.Value).Split(','); finalSQL = "select " + subTableInfo[1] + "." + (String)requestData["emailID"] + " from " + subTableInfo[1] + " inner join " + (String)requestData["xref"] + " on " + subTableInfo[1] + "." + subTableInfo[2] + "=" + (String)requestData["xref"] + "." + subTableInfo[4] + " inner join " + subTableInfo[0] + " on " + (String)requestData["xref"] + "." + subTableInfo[5] + "=" + subTableInfo[0] + "." + subTableInfo[5] + " and " + subTableInfo[0] + "." + subTableInfo[3] + " in ("; theComma = ""; foreach (var subTable2 in requestData) { if (subTable2.Key.IndexOf("|") > -1) { finalSQL += theComma + "'" + ((String)subTable2.Value).Replace("'", "''") + "'"; theComma = ","; } } } } finalSQL += ")"; result = theDB.execQuery(finalSQL); if (result.Read()) { for (int i = 0; i < result.FieldCount; i++) { try { if(!(string.IsNullOrEmpty(result.GetString(i)))) message.Bcc.Add(result.GetString(i)); } catch (Exception ex) { } } } result.Close(); var smtp = new SmtpClient { Host = "mailhub.ppl.com", Port = 25, EnableSsl = false, DeliveryMethod = SmtpDeliveryMethod.Network, UseDefaultCredentials = true, //Credentials = new NetworkCredential(fromAddress.Address, fromPassword) }; smtp.Send(message); status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //response.Add("success", "true"); //Response.Write(rs.Serialize(response)); break; case "Save": // This only currently deals with one key... need to split on ":" for more than one. if (((String)requestData[(String)requestData["form:key"]]) == "") { // Generate Key switch ((String)requestData["form:keygen"]) { case "UUID": requestData[(String)requestData["form:key"]] = System.Guid.NewGuid().ToString(); break; case "MAX": // Add select max logic when needed break; } // Do Data Validation Here // ... and I know what you're going to say, but I like Dynamic SQL. // Dynamically build query: // to-do: harden "table" from sql injection. sql = "select * from " + (String)requestData["form:dataSource"] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "insert into " + (String)requestData["form:dataSource"] + " ("; sql2 = ") values ("; theComma = ""; for (int i = 0; i < resultA.FieldCount; i++) { if (requestData.ContainsKey(resultA.GetName(i))) { sql += theComma + resultA.GetName(i); if (resultA.GetFieldType(i) == typeof(DateTime)) { if ((String)requestData[resultA.GetName(i)] == "") { sql2 += theComma + "null"; } else { sql2 += theComma + "to_date('" + ((String)requestData[resultA.GetName(i)]).Replace("'", "''") + "','MM/DD/YYYY')"; } } else { if ((String)requestData[resultA.GetName(i)] == "") { sql2 += theComma + "null"; } else { sql2 += theComma + "'" + ((String)requestData[resultA.GetName(i)]).Replace("'", "''") + "'"; } } theComma = ", "; } } resultA.Close(); finalSQL = sql + sql2 + ")"; response.Add("datakey", (String)requestData[(String)requestData["form:key"]]); if (theDB.execNonQuery(finalSQL) == true) { response.Add("success", "true"); } else { response.Add("success", "false"); response.Add("message", "A SQL Error has occured.\nA member of the support staff has been notified."); } status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(response)); } else { // Dynamically build query: sql = "select * from " + (String)requestData["form:dataSource"] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "update " + (String)requestData["form:dataSource"] + " set "; sql2 = ""; theComma = ""; for (int i = 0; i < resultA.FieldCount; i++) { if (requestData.ContainsKey(resultA.GetName(i))) { if (resultA.GetName(i) == (String)requestData["form:key"]) { // Do nothing } else if (resultA.GetFieldType(i) == typeof(DateTime)) { if ((String)requestData[resultA.GetName(i)] == "") { sql2 += theComma + resultA.GetName(i) + " = null"; } else { sql2 += theComma + resultA.GetName(i) + " = to_date('" + ((String)requestData[resultA.GetName(i)]).Replace("'", "''") + "','MM/DD/YYYY')"; } } else { sql2 += theComma + resultA.GetName(i) + " = '" + ((String)requestData[resultA.GetName(i)]).Replace("'", "''") + "'"; } if (!sql2.Equals("")) { theComma = ", "; } } } resultA.Close(); finalSQL = sql + sql2 + " where " + (String)requestData["form:key"] + " = '" + ((String)requestData[(String)requestData["form:key"]]).Replace("'", "''") + "'"; response.Add("datakey", ((String)requestData[(String)requestData["form:key"]])); if (theDB.execNonQuery(finalSQL) == true) { response.Add("success", "true"); } else { response.Add("success", "false"); response.Add("message", "A SQL Error has occured.\nA member of the support staff has been notified."); } status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(response)); } // Sub Tables foreach (var subTable in requestData) { if (subTable.Key.IndexOf("xrefSubform") > -1) { String subField = subTable.Key.Substring(0, subTable.Key.IndexOf("xrefSubform")); // subTableInfo variable needs to be hardened against SQL injection String[] subTableInfo = ((String)subTable.Value).Split(','); // Delete Existing Data sql = "select * from " + subTableInfo[1] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "delete from " + subTableInfo[1] + " where " + subTableInfo[2] + " = "; sql2 = ""; theComma = ""; for (int i = 0; i < resultA.FieldCount; i++) { if (((String)subTableInfo[2]).Equals(resultA.GetName(i))) { if (resultA.GetFieldType(i) == typeof(DateTime)) { sql2 += theComma + "TO_CHAR(" + (String)requestData[(String)requestData["form:key"]] + ", 'MM/DD/YYYY')"; } else if (resultA.GetFieldType(i) != typeof(String)) { sql2 += theComma + "TO_CHAR(" + (String)requestData[(String)requestData["form:key"]] + ")"; } else { sql2 += theComma + "'" + (String)requestData[(String)requestData["form:key"]] + "'"; } if (!sql2.Equals("")) { theComma = " and "; } } } resultA.Close(); finalSQL = sql + sql2; theDB.execNonQuery(finalSQL); // Insert new records foreach (var subRecord in requestData) { if (subRecord.Key.IndexOf(subField + "|") == 0) { sql = "select * from " + subTableInfo[1] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "insert into " + subTableInfo[1] + " ("; sql2 = ") values ("; theComma = ""; for (int i = 0; i < resultA.FieldCount; i++) { if (resultA.GetName(i).Equals(subTableInfo[2]) || resultA.GetName(i).Equals(subTableInfo[3])) { sql += theComma + resultA.GetName(i); if (resultA.GetFieldType(i) == typeof(DateTime)) { if (resultA.GetName(i).Equals(subTableInfo[3])) { sql2 += theComma + "to_date('" + ((String)subRecord.Value).Replace("'", "''") + "','MM/DD/YYYY')"; } else { sql2 += theComma + "to_date('" + ((String)requestData[(String)requestData["form:key"]]).Replace("'", "''") + "','MM/DD/YYYY')"; } } else { if (resultA.GetName(i).Equals(subTableInfo[3])) { sql2 += theComma + "'" + ((String)subRecord.Value).Replace("'", "''") + "'"; } else { sql2 += theComma + "'" + ((String)requestData[(String)requestData["form:key"]]).Replace("'", "''") + "'"; } } theComma = ", "; } } resultA.Close(); finalSQL = sql + sql2 + ")"; theDB.execNonQuery(finalSQL); } } } } break; case "Delete": // Dynamically build query: sql = "select * from " + (String)requestData["form:dataSource"] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "delete from " + (String)requestData["form:dataSource"] + " where " + (String)requestData["form:key"] + " = "; sql2 = ""; theComma = ""; for (int i = 0; i < resultA.FieldCount; i++) { if (((String)requestData["form:key"]).Equals(resultA.GetName(i))) { if (resultA.GetFieldType(i) == typeof(DateTime)) { sql2 += theComma + "TO_CHAR(" + (String)requestData[(String)requestData["form:key"]] + ", 'MM/DD/YYYY')"; } else if (resultA.GetFieldType(i) != typeof(String)) { sql2 += theComma + "TO_CHAR(" + (String)requestData[(String)requestData["form:key"]] + ")"; } else { sql2 += theComma + "'" + (String)requestData[(String)requestData["form:key"]] + "'"; } if (!sql2.Equals("")) { theComma = " and "; } } } resultA.Close(); finalSQL = sql + sql2; if (theDB.execNonQuery(finalSQL) == true) { response.Add("success", "true"); } else { response.Add("success", "false"); response.Add("message", "A SQL Error has occured.\nA member of the support staff has been notified."); } status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(response)); break; case "Load": // Dynamically build query: sql = "select * from " + (String)requestData["form:dataSource"] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "select "; sql2 = ""; theComma = ""; for (int i = 0; i < resultA.FieldCount; i++) { if (requestData.ContainsKey(resultA.GetName(i))) { if (resultA.GetFieldType(i) == typeof(DateTime)) { sql2 += theComma + "TO_CHAR(" + resultA.GetName(i) + ", 'MM/DD/YYYY') " + resultA.GetName(i); } else if (resultA.GetFieldType(i) != typeof(String)) { sql2 += theComma + "TO_CHAR(" + resultA.GetName(i) + ") " + resultA.GetName(i); } else { sql2 += theComma + resultA.GetName(i); } if (!sql2.Equals("")) { theComma = ", "; } } } resultA.Close(); finalSQL = sql + sql2 + " from " + (String)requestData["form:dataSource"] + " where " + (String)requestData["form:key"] + " = '" + ((String)requestData[(String)requestData["form:key"]]).Replace("'", "''") + "'"; //finalSQL = "select * from (" + finalSQL + ") where ROWNUM <= 100"; result = theDB.execQuery(finalSQL); if (result.Read()) { for (int i = 0; i < result.FieldCount; i++) { String theValue = ""; try { theValue = result.GetString(i); } catch (Exception ex) { } response.Add(result.GetName(i), theValue); } } result.Close(); status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", response); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(response)); break; case "LoadSelect": case "Search": case "Export": // This feature is not fully implemented // Do NOT USE for Export! if(action.Equals("Export")) { Response.AddHeader("Content-Disposition", "attachment; filename=export.csv"); //Response.AddHeader("Content-Length", file.Length.ToString()); Response.ContentType = "text/plain"; } // Dynamically build query: sql = "select * from " + (String)requestData["form:dataSource"] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "select "; sql2 = ""; sql3 = ""; sql4 = " order by "; theComma = ""; String exportLine = ""; ArrayList returnFields = (ArrayList)requestData["return:fields"]; String theComma3 = ""; for (int i = 0; i < resultA.FieldCount; i++) { if (returnFields.Contains(resultA.GetName(i)) || requestData.ContainsKey(resultA.GetName(i))) { if (resultA.GetFieldType(i) == typeof(DateTime)) { sql2 += theComma + "TO_CHAR(" + resultA.GetName(i) + ", 'MM/DD/YYYY') " + resultA.GetName(i); if (requestData.ContainsKey(resultA.GetName(i))) { if (((String)requestData[resultA.GetName(i)]) == "null") { sql3 += theComma3 + resultA.GetName(i) + " is null"; } else if (((String)requestData[resultA.GetName(i)]) != "") { sql3 += theComma3 + resultA.GetName(i) + " = to_date('" + ((String)requestData[resultA.GetName(i)]).Replace("'", "''") + "','MM/DD/YYYY')"; } } } else if (resultA.GetFieldType(i) != typeof(String)) { sql2 += theComma + "TO_CHAR(" + resultA.GetName(i) + ") " + resultA.GetName(i); if (requestData.ContainsKey(resultA.GetName(i))) { if (((String)requestData[resultA.GetName(i)]) != "") { sql3 += theComma3 + resultA.GetName(i) + " = '" + ((String)requestData[resultA.GetName(i)]).Replace("'", "''") + "'"; } } } else { sql2 += theComma + resultA.GetName(i); if (requestData.ContainsKey(resultA.GetName(i))) { if (((String)requestData[resultA.GetName(i)]) != "") { sql3 += theComma3 + resultA.GetName(i) + " like'%" + ((String)requestData[resultA.GetName(i)]).Replace("'", "''") + "%'"; } } } if (!sql2.Equals("")) { theComma = ", "; } if (!sql3.Equals("")) { theComma3 = " and "; } } } resultA.Close(); finalSQL = sql + sql2 + " from " + (String)requestData["form:dataSource"]; if (sql3 != "") { finalSQL += " where " + sql3; } //sql = "select CONTACTID, FIRSTNAME, LASTNAME, ADDR1, ADDR2, CITY, STATE, ZIP, PHONE from CONTACT where FIRSTNAME like'%" + ((String)requestData["FIRSTNAME"]).Replace("'", "''") + "%' and LASTNAME like '%" + ((String)requestData["LASTNAME"]).Replace("'", "''") + "%'"; finalSQL = "select * from (" + finalSQL + ")"; // where ROWNUM <= 500"; result = theDB.execQuery(finalSQL); if (result != null) { while (result.Read()) { response = new Dictionary<String, String>(); String comma = ""; exportLine = ""; for (int i = 0; i < result.FieldCount; i++) { String theValue = ""; try { theValue = result.GetString(i); } catch (Exception ex) { } if(action.Equals("Export")) { exportLine += comma + "\"" + theValue.Replace("\"", "\"\"") + "\""; comma = ","; } else { response.Add(result.GetName(i), theValue); } } if(action.Equals("Export")) { Response.Write(exportLine + "\n"); } else { responses.Add(response); } } result.Close(); if(!action.Equals("Export")) { responses.Sort(delegate(Dictionary<String, String> d1, Dictionary<String, String> d2) { return d1[(String)((ArrayList)requestData["return:fields"])[1]].CompareTo(d2[(String)((ArrayList)requestData["return:fields"])[1]]); }); status.Add("result", "true"); status.Add("message", ""); } } else { //response = new Dictionary<String, String>(); status.Add("result", "false"); status.Add("message", "A SQL Error has occured.\nA member of the support staff has been notified."); //response.Add("success", "false"); //response.Add("message", "A SQL Error has occured.\nA member of the support staff has been notified."); //responses.Add(response); } if(!action.Equals("Export")) { rs.MaxJsonLength = rs.MaxJsonLength * 2; responsePackage.Add("data", responses); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(responses)); } break; case "subsearch": // Dynamically build query: sql = "select * from " + (String)requestData["sub:dataSource"] + " where 1=2"; resultA = theDB.execQuery(sql); sql = "select "; sql2 = ""; sql3 = ""; theComma = ""; returnFields = (ArrayList)requestData["return:fields"]; theComma3 = ""; for (int i = 0; i < resultA.FieldCount; i++) { if (returnFields.Contains(resultA.GetName(i)) || requestData.ContainsKey(resultA.GetName(i))) { if (resultA.GetFieldType(i) == typeof(DateTime)) { sql2 += theComma + "TO_CHAR(sec." + resultA.GetName(i) + ", 'MM/DD/YYYY') sec." + resultA.GetName(i); } else if (resultA.GetFieldType(i) != typeof(String)) { sql2 += theComma + "TO_CHAR(sec." + resultA.GetName(i) + ") sec." + resultA.GetName(i); } else { sql2 += theComma + "sec." + resultA.GetName(i); } if (!sql2.Equals("")) { theComma = ", "; } if (!sql3.Equals("")) { theComma3 = " and "; } } } resultA.Close(); // Need to change this line to include different columns for the join (they could be called different things on different tables). // All of this needs to be validated against the table just like the return columns. finalSQL = sql + sql2 + " from " + (String)requestData["sub:dataSource"] + " sec inner join " + (String)requestData["sub:parentDataSource"] + " pri on " + "sec." + (String)requestData["sub:childKey"] + " = pri." + (String)requestData["key"] + " where " + "pri." + (String)requestData["sub:parentReference"] + " = '" + (String)requestData["sub:Key"] + "'"; /* if (sql3 != "") { finalSQL += " where " + sql3; } */ //sql = "select CONTACTID, FIRSTNAME, LASTNAME, ADDR1, ADDR2, CITY, STATE, ZIP, PHONE from CONTACT where FIRSTNAME like'%" + ((String)requestData["FIRSTNAME"]).Replace("'", "''") + "%' and LASTNAME like '%" + ((String)requestData["LASTNAME"]).Replace("'", "''") + "%'"; //finalSQL = "select * from (" + finalSQL + ") where ROWNUM <= 100"; result = theDB.execQuery(finalSQL); if (result != null) { while (result.Read()) { response = new Dictionary<String, String>(); for (int i = 0; i < result.FieldCount; i++) { String theValue = ""; try { theValue = result.GetString(i); } catch (Exception ex) { } response.Add(result.GetName(i), theValue); } responses.Add(response); } result.Close(); responses.Sort(delegate(Dictionary<String, String> d1, Dictionary<String, String> d2) { return d1[(String)((ArrayList)requestData["return:fields"])[1]].CompareTo(d2[(String)((ArrayList)requestData["return:fields"])[1]]); }); status.Add("result", "true"); status.Add("message", ""); } else { //response = new Dictionary<String, String>(); status.Add("result", "false"); status.Add("message", "A SQL Error has occured.\nA member of the support staff has been notified."); //response.Add("success", "false"); //response.Add("message", "A SQL Error has occured.\nA member of the support staff has been notified."); //responses.Add(response); } //status.Add("result", "true"); //status.Add("message", ""); responsePackage.Add("data", responses); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(responses)); break; case "xxxxxLoadSelect": // Dynamically build query: sql = "select * from " + request + " where 1=2"; resultA = theDB.execQuery(sql); sql = "select "; sql2 = ""; theComma = ""; for (int i = 0; i < resultA.FieldCount; i++) { if ((String)requestData["key"] == resultA.GetName(i) || (String)requestData["value"] == resultA.GetName(i)) { if (resultA.GetFieldType(i) == typeof(DateTime)) { sql2 += theComma + "TO_CHAR(" + resultA.GetName(i) + ", 'MM/DD/YYYY') " + resultA.GetName(i); } else if (resultA.GetFieldType(i) != typeof(String)) { sql2 += theComma + "TO_CHAR(" + resultA.GetName(i) + ") " + resultA.GetName(i); } else { sql2 += theComma + resultA.GetName(i); } if ((String)requestData["key"] == resultA.GetName(i)) { sql2 += " KEY "; } else if ((String)requestData["value"] == resultA.GetName(i)) { sql2 += " VALUE "; } if (!sql2.Equals("")) { theComma = ", "; } } } resultA.Close(); finalSQL = sql + sql2 + " from " + request; if ((String)requestData["filter"] != "") { finalSQL += " where " + ((String)requestData["filter"]).Replace("'", "''") + "'"; } //finalSQL = "select * from (" + finalSQL + ") where ROWNUM <= 100"; result = theDB.execQuery(finalSQL); if (result != null) { while (result.Read()) { response = new Dictionary<String, String>(); for (int i = 0; i < result.FieldCount; i++) { String theValue = ""; try { theValue = result.GetString(i); } catch (Exception ex) { } response.Add(result.GetName(i), theValue); } responses.Add(response); } result.Close(); responses.Sort(delegate(Dictionary<String, String> d1, Dictionary<String, String> d2) { return d1["VALUE"].CompareTo(d2["VALUE"]); }); } result.Close(); status.Add("result", "true"); status.Add("message", ""); responsePackage.Add("data", responses); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(rs.Serialize(responses)); break; } theDB.CloseConnection(); } break; } } catch (Exception ex) { status.Add("result", "false"); status.Add("message", ex.Message + "\n" + ex.StackTrace); responsePackage.Add("data", ""); responsePackage.Add("status", status); Response.Write(rs.Serialize(responsePackage)); //Response.Write(ex.Message + "\n" + ex.StackTrace); } }