How to add SINGLE QUOTE to each array element when pass to PHP via ajax?

Question

I have created a drop down menu with checkbox options. The idea is when user select one or more checkboxes, the checked value should be passed to an SQL statement in a php page through AJAX. However, my sql does not seem to generate the result as it should be. My SQL statement is using an IN operator where the checked value should be inside the WHERE clause. For example, if I select CONSTRUCTION, HCL and RSS from the drop the menu checkboxes, the SQL statement in the php page should be SELECT * FROM STAFF WHERE SERVICE IN ('CONSTRUCTION', 'HCL', 'RSS'). However, when i echo my SQL statement, it was showing the checked values without the single quote - SELECT * FROM STAFF WHERE SERVICE IN (CONSTRUCTION, HCL, RS)which is incorrect. How can i include the single quotes for each checked values from the drop down for the IN operator? Can anyone help?

Below is my PHP and the drop down menu

sql.php

<?php

if($_POST['dataarray'] != ""){

$sql = "SELECT * FROM STAFF WHERE SERVICE IN (".$_POST['dataarray'].")";
.....
...
/*************SQL output*********************/
}

?>

Drop Down Menu with checkboxes

$(document).ready(function(){

/*********************convert select into multiselect************************/
$("#service").CreateMultiCheckBox({ width: '300px', defaultText : 'Select Below', height:'auto' }); 

    $("#service").on('change', function (){
                    var dataarray = [];
                
                    $("#service option").each(function(){
                        if($(this).is(":checked"))
                        {dataarray.push($(this).val());}    
                    }); 
                    
                    dataarray = dataarray.toString(); 
                 $.ajax({
                          url: "sql.php",
                          method:"POST",
                          data: {dataarray:dataarrayy},
                          success: function(data){                          
                          }             
                        });                 

                });
            
/**********************creating of checkboxes for each select option************************/
 $(document).on("click", ".MultiCheckBox", function () {
                var detail = $(this).next();
                detail.show();
            });

            $(document).on("click", ".MultiCheckBoxDetailHeader input", function (e) {
                e.stopPropagation();
                var hc = $(this).prop("checked");
                $(this).closest(".MultiCheckBoxDetail").find(".MultiCheckBoxDetailBody input").prop("checked", hc);
                $(this).closest(".MultiCheckBoxDetail").next().UpdateSelect();
            });

            $(document).on("click", ".MultiCheckBoxDetailHeader", function (e) {
                var inp = $(this).find("input");
                var chk = inp.prop("checked");
                inp.prop("checked", !chk);
                $(this).closest(".MultiCheckBoxDetail").find(".MultiCheckBoxDetailBody input").prop("checked", !chk);
                $(this).closest(".MultiCheckBoxDetail").next().UpdateSelect();
            });

            $(document).on("click", ".MultiCheckBoxDetail .cont input", function (e) {
                e.stopPropagation();
                $(this).closest(".MultiCheckBoxDetail").next().UpdateSelect();

                var val = ($(".MultiCheckBoxDetailBody input:checked").length == $(".MultiCheckBoxDetailBody input").length)
                $(".MultiCheckBoxDetailHeader input").prop("checked", val);
            });

            $(document).on("click", ".MultiCheckBoxDetail .cont", function (e) {
                var inp = $(this).find("input");
                var chk = inp.prop("checked");
                inp.prop("checked", !chk);

                var multiCheckBoxDetail = $(this).closest(".MultiCheckBoxDetail");
                var multiCheckBoxDetailBody = $(this).closest(".MultiCheckBoxDetailBody");
                multiCheckBoxDetail.next().UpdateSelect();

                var val = ($(".MultiCheckBoxDetailBody input:checked").length == $(".MultiCheckBoxDetailBody input").length)
                $(".MultiCheckBoxDetailHeader input").prop("checked", val);
            });

            $(document).mouseup(function (e) {
                var container = $(".MultiCheckBoxDetail");
                if (!container.is(e.target) && container.has(e.target).length === 0) {
                    container.hide();
                }
            });
        });

        var defaultMultiCheckBoxOption = { width: '220px', defaultText: 'Select Below', height: '200px' };

        jQuery.fn.extend({
            CreateMultiCheckBox: function (options) {

                var localOption = {};
                localOption.width = (options != null && options.width != null && options.width != undefined) ? options.width : defaultMultiCheckBoxOption.width;
                localOption.defaultText = (options != null && options.defaultText != null && options.defaultText != undefined) ? options.defaultText : defaultMultiCheckBoxOption.defaultText;
                localOption.height = (options != null && options.height != null && options.height != undefined) ? options.height : defaultMultiCheckBoxOption.height;

                this.hide();
                this.attr("multiple", "multiple");
                var divSel = $("<div class='MultiCheckBox'>" + localOption.defaultText + "<span class='k-icon k-i-arrow-60-down'><svg aria-hidden='true' focusable='false' data-prefix='fas' data-icon='sort-down' role='img' xmlns='http://www.w3.org/2000/svg' viewBox='0 0 320 512' class='svg-inline--fa fa-sort-down fa-w-10 fa-2x'><path fill='currentColor' d='M41 288h238c21.4 0 32.1 25.9 17 41L177 448c-9.4 9.4-24.6 9.4-33.9 0L24 329c-15.1-15.1-4.4-41 17-41z' class=''></path></svg></span></div>").insertBefore(this);
                divSel.css({ "width": localOption.width });

                var detail = $("<div class='MultiCheckBoxDetail'><div class='MultiCheckBoxDetailHeader'><input type='checkbox' class='mulinput' value='-1982' /><div>Select All</div></div><div class='MultiCheckBoxDetailBody'></div></div>").insertAfter(divSel);
                detail.css({ "width": parseInt(options.width) + 10, "max-height": localOption.height });
                var multiCheckBoxDetailBody = detail.find(".MultiCheckBoxDetailBody");

                this.find("option").each(function () {
                    var val = $(this).attr("value");

                    if (val == undefined)
                        val = '';

                    multiCheckBoxDetailBody.append("<div class='cont'><div><input type='checkbox' class='mulinput' value='" + val + "' /></div><div>" + $(this).text() + "</div></div>");
                });

                multiCheckBoxDetailBody.css("max-height", (parseInt($(".MultiCheckBoxDetail").css("max-height")) - 28) + "px");
            },
            UpdateSelect: function () {
                var arr = [];

                this.prev().find(".mulinput:checked").each(function () {
                    arr.push($(this).val());
                });

                this.val(arr).change();
            },

});

.MultiCheckBox {
            border:1px solid #e2e2e2;
            padding: 5px;
            border-radius:4px;
            cursor:pointer;
            background:#ffffff;
        }

        .MultiCheckBox .k-icon{ 
            font-size: 15px;
            float: right;
            font-weight: bolder;
            margin-top: -7px;
            height: 10px;
            width: 14px;
            color:#787878;
        } 

        .MultiCheckBoxDetail {
            display:none;
            position:absolute;
            border:1px solid #e2e2e2;
            overflow-y:hidden;
            background:#ffffff;
        }

        .MultiCheckBoxDetailBody {
            overflow-y:scroll;
        }

            .MultiCheckBoxDetail .cont  {
                clear:both;
                overflow: hidden;
                padding: 2px;
            }

            .MultiCheckBoxDetail .cont:hover  {
                background-color:#cfcfcf;
            }

            .MultiCheckBoxDetailBody > div > div {
                float:left;
            }

        .MultiCheckBoxDetail>div>div:nth-child(1) {
        
        }

        .MultiCheckBoxDetailHeader {
            overflow:hidden;
            position:relative;
            height: 28px;
            background-color:#3d3d3d;
        }

            .MultiCheckBoxDetailHeader>input {
                position: absolute;
                top: 4px;
                left: 3px;
            }

            .MultiCheckBoxDetailHeader>div {
                position: absolute;
                top: 5px;
                left: 24px;
                color:#fff;
            }

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<select name="service" class="service" id="service">
  <option value="CONSTRUCTION">Construction</option>
  <option value="HCL">HCL</option>
  <option value="MANUFACTURING">Manufacturing</option>
  <option value="MYE">MYE</option>
  <option value="RSS">RSS</option>
  <option value="SERVICE">SERVICE</option>
  <option value="NA">NA</option>
</select>

Don't call dataarray.toString() in the ajax side, pass the array as is and quote the elements in the php side. — msg
– msg, Commented Sep 2, 2020 at 10:49
Do not insert the passed data directly in you sql statement. Just use prepared statements for this kind of database statements. — Marcel
– Marcel, Commented Sep 2, 2020 at 10:57
data: {dataarray:dataarrayy} dataarrayy is not defined kindly correct the spell of datarrayy as dataarray. — Kamran Allana
– Kamran Allana, Commented Sep 2, 2020 at 11:01

xtothea · Accepted Answer · 2020-09-02 10:59:21Z

1

What you are doing there is very risky. You are putting the $_POST input directly into your sql-Query. This can lead to serious mysql-injections issues.

You should sanitize your $_POST data first. For instance use Methods like stripslashes to remove unwanted characters that can lead to sql-injections.

Or use a database wrapper that uses prepared statements like Pdo

To answer your String related question:

You can iterate over your data array and wrap the string with quotes: E.g.

$dataArray = [];
foreach ($_POST['dataarray'] as $item) {
    $dataArray[] = "'" . $item . "'";
}

$sql = "SELECT * FROM STAFF WHERE SERVICE IN (". implode(', ', $dataArray) .")";

answered Sep 2, 2020 at 10:59

xtothea

1691 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add SINGLE QUOTE to each array element when pass to PHP via ajax?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related