Incorrect syntax near ':'.

Question

1.00/5 (1 vote)

See more:

Hello,

Appreciate some "Guru" advise, try to solve this problem.

public class Service1 : System.Web.Services.WebService
    {

        [WebMethod]
        public string VoidTransaction (string merchantcode, string cctransid, string amount, string currency, string signature)
        {

            string dbKey = "";
            string dbMerchantId = "";

            if (merchantcode == "")
            {
                return "Merchant Code is empty";
            }
            else if (cctransid == "")
            {
                return "Transaction ID is empty";
            }
            else if (amount == "")
            {
                return "Amount is empty";
            }
            else if (currency == "")
            {
                return "Currency is empty";
            }
            else if (signature == "")
            {
                return "Signature is empty";
            }
            else
            {
                string ConnectionString = "";
                SqlConnection myConnection;

                myConnection = new SqlConnection(ConnectionString);
                myConnection.ConnectionString = WebConfigurationManager.AppSettings["WebSvcDb"];
                myConnection.Open();

                string sqlGetKey = ("SELECT MerchantKey, MerchantId from eMerchant where MerchantCode = '"+merchantcode+ "'");

                SqlCommand cmdGet = new SqlCommand(sqlGetKey, myConnection);
                SqlDataReader drGet = cmdGet.ExecuteReader();

                if (drGet.Read())
                {
                    dbKey = drGet["MerchantKey"].ToString();
                    dbMerchantId = drGet["MerchantId"].ToString();
                }

                drGet.Close();
                myConnection.Close();
            }

            if (signature != "")
            {
                string tempAmount = "0.00";
                tempAmount = amount.Replace(",", "").Replace(".", "");

                string tempString = dbKey + merchantcode + cctransid + tempAmount + currency;

                string tempSign = Security(tempString);

                if (tempSign != signature)
                {
                    return "Signature not match";
                }
            }

            return CheckVoidFunction(merchantcode, cctransid, amount, currency, dbMerchantId);
        }

        private string CheckVoidFunction(string merchantcode, string transactionid, string amount, string currency, string MerId)
        {
            string ConnectionString = "";
            string dbPayId = "";
            string dbMiddetails = "";
            string dbIdentityCode = "";
            string dbAmount = "";
            string dbComments = "";
            string dbPaybank = "";
            string dbCurrency = "";
            string dbMerchantName = "";
            string dbMerchantId = "";

            SqlConnection myConnection;

            myConnection = new SqlConnection(ConnectionString);
            myConnection.ConnectionString = WebConfigurationManager.AppSettings["WebSvcDb"];
            myConnection.Open();

            string sqlGetTransactionDetail = ("SELECT p.PayId, p.MidDetails, p.IdentityCode, p.Amount, p.Comments, p.Paybank, p.Currency, p.MerchantId, m.MerchantName from ePayment p, eMerchant m where m.MerchantId = p.MerchantId and p.CCTransId = '" + transactionid + "'");

            SqlCommand cmd = new SqlCommand(sqlGetTransactionDetail, myConnection);
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                dbPayId = dr["MidDetails"].ToString();
                dbMiddetails = dr["MidDetails"].ToString();
                dbIdentityCode = dr["IdentityCode"].ToString();
                dbAmount = dr["Amount"].ToString();
                dbComments = dr["Comments"].ToString();
                dbPaybank = dr["Paybank"].ToString();
                dbCurrency = dr["Currency"].ToString();
                dbMerchantId = dr["MerchantId"].ToString();
                dbMerchantName = dr["MerchantName"].ToString();

                dr.Close();
                myConnection.Close();

                if (decimal.Round(decimal.Parse(dbAmount.ToString()), 2).ToString() != amount)
                {
                    return "Invalid Amount";
                }
                else if (dbCurrency != currency)
                {
                    return "Invalid Currency";
                }
                else if (dbMerchantId != MerId)
                {
                    return "Invalid Merchant Code";
                }
                else
                {
                    if (dbPaybank == "44")
                    {
                        return PBBVoidFunction(merchantcode, transactionid, amount, currency, dbPayId, dbMiddetails, dbIdentityCode, dbAmount, dbComments, dbPaybank, dbMerchantName);
                    }
                    else
                    {
                        return "This transaction is not eligible for voiding";
                    }
                }
            }
            else
            {
                dr.Close();
                myConnection.Close();

                return "Transaction not found";
            }
        }

        private string PBBVoidFunction(string merchantcode, string transactionid, string amount, string currency, string dbPayId, string dbMiddetails, string dbIdentityCode, string dbAmount, string dbComments, string dbPaybank, string dbMerchantName)
        {

            string ConnectionString = "";
           
            string BinNum = "";
            string VisaMID = "";
            string MasterMID = "";
            string TID = "";
            string LiveStatus = "";

            string PadTransId = "";
            string PadAmount = "";
            string QSTRING = "";
            string tempURL = "";

            string merID = "";

            string tempResult = "";
            string sqlUpdateTransaction = "";

            if (dbIdentityCode != "")
            {
                BinNum = dbIdentityCode.Substring(0, 6);
            }

            if (dbMiddetails != "")
            {
                string[] stringSeparators = new string[] { ":||:" };
                string[] result = dbMiddetails.Split(stringSeparators, StringSplitOptions.None);

                if (result.Length > 0)
                {
                    VisaMID = result[0];
                    MasterMID = result[1];
                    TID = result[2];
                    LiveStatus = result[3];
                }
            }

            if (BinNum.Substring(0, 1) == "4")
            {
                merID = VisaMID;
            }
            else if (BinNum.Substring(0, 1) == "5")
            {
                merID = MasterMID;
            }
            PadTransId = transactionid.PadLeft(20, '0');
            PadAmount = decimal.Round(decimal.Parse(dbAmount.ToString()), 2).ToString().Replace(".", "").Replace(",", "").PadLeft(12, '0');

            QSTRING = "transactionType=0400&merID=" + merID + "&invoiceNo=" + PadTransId + "&amount=" + PadAmount;

            if (LiveStatus == "0")
            {
                tempURL = "https://uattds2.pbebank.com/PGW/Pay/Process";
            }
            else
            {
                tempURL = "https://ecom.pbebank.com/PGW/Pay/Process";
            }


            ServicePointManager.ServerCertificateValidationCallback = delegate { return true; };
            ServicePointManager.Expect100Continue = true;

            ServicePointManager.SecurityProtocol = (SecurityProtocolType)(0xc0 | 0x300 | 0xc00);

                   // Create a request using a URL that can receive a post. 
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(tempURL);
            // Set the Method property of the request to POST.
            request.Method = "POST";
            // Create POST data and convert it to a byte array.
            byte[] byteArray = Encoding.UTF8.GetBytes(QSTRING);
            // Set the ContentType property of the WebRequest.
            request.ContentType = "application/x-www-form-urlencoded";
            // Set the ContentLength property of the WebRequest.
            request.ContentLength = byteArray.Length;
            // Get the request stream.
            Stream dataStream = request.GetRequestStream();
            // Write the data to the request stream.
            dataStream.Write(byteArray, 0, byteArray.Length);
            // Close the Stream object.
            dataStream.Close();
            // Get the response.
            WebResponse response = request.GetResponse();
            // Get the stream containing content returned by the server.
            dataStream = response.GetResponseStream();
            // Open the stream using a StreamReader for easy access.
            StreamReader reader = new StreamReader(dataStream);
            // Read the content.
            string responseFromServer = reader.ReadToEnd();
            // Clean up the streams.
            reader.Close();
            dataStream.Close();
            response.Close();

            string PBBresponse = "";
            string PBBauthCode = "";
            string PNNinvoiceNo = "";
            string PBBPAN = "";
            string PBBExpDate = "";
            string PBBAmount = "";

            PBBresponse = responseFromServer.Substring(0, 2);
            PBBauthCode = responseFromServer.Substring(2, 6);
            PNNinvoiceNo = responseFromServer.Substring(8, 20);
            PBBPAN = responseFromServer.Substring(28, 4);
            PBBExpDate = responseFromServer.Substring(32, 4);
            PBBAmount = responseFromServer.Substring(36, 12);

            dbComments = dbComments + "iPay88:VoidTransaction|" +
            "iPay88Date:" + DateTime.UtcNow.AddHours(8) + "|" +
            "response:" + PBBresponse + "|" +
            "authCode:" + PBBauthCode + "|" +
            "invoiceNo:" + PNNinvoiceNo + "|" +
            "PAN:" + PBBPAN + "|" +
            "ExpDate:" + PBBExpDate + "|" +
            "Amount:" + PBBAmount + "||";

            if (PBBresponse == "00")
            {
                sqlUpdateTransaction = "Update ePayment set PayStatus = 17, Comments = '" + dbComments + "' where PayId = " + dbPayId + "";
            }
            else
            {
                sqlUpdateTransaction = "Update ePayment set Comments = '" + dbComments + "' where PayId = " + dbPayId + "";
            }

            tempResult = PBBresponse;

            if (sqlUpdateTransaction != "")
            {
                SqlConnection myConnection;

                myConnection = new SqlConnection(ConnectionString);
                myConnection.ConnectionString = WebConfigurationManager.AppSettings["WebSvcDb"];
                myConnection.Open();

                SqlCommand cmdUpdate = new SqlCommand(sqlUpdateTransaction, myConnection);
                cmdUpdate.ExecuteNonQuery();

                myConnection.Close();
            }
            return tempResult;
        }

Thank you.

Any pointers are grateful.

Thanks

Rickson

What I have tried:

ServicePointManager.SecurityProtocol = (SecurityProtocolType)(0xc0 | 0x300 | 0xc00);

Posted 6-Apr-20 23:46pm

Member 14794855

Updated 7-Apr-20 0:26am

Add a Solution

Comments

Richard MacCutchan 7-Apr-20 5:48am

So somewhere in all that code is a colon that should not be there. But you did not think it important enough to tell us exactly where it is. I can only assume that since you are creating all your SQL statements the wrong way, that it is somewhere in there.

Patrice T 7-Apr-20 7:00am

Give exact error message, it also tells the line number.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-04-07T00:27:00

Don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fix that throughout your whole app - miss one, and someone will find it! - and the chances are your error will vanish at the same time.