Question 20159 in Mysql Question posted in
The official MySQL documentation can be found here.

Convert HTML Code with quotation marks to string for SQL insertion – Mysql

Let’s take this sample HTML:

    <div class="card custom-card mt-3">
        <div class="card-body" id="default">
            <div class="row">
                <div class="col-lg-8">
                    <input type='text' name="project_code" placeholder="Dear Client," class="form-control" onkeyup="setvalue(this)" id="text" />
                </div>
            </div>
        </div>
    </div>
    <script>
    var counterSection = 0;

    function init() {
        
        dragula([document.getElementById('default')]).on('drag', function (el) {
                el.className = el.className.replace('draggable', '');
            }).on('drop', function (el) {
                el.className += ' draggable';
            });
    </script>

It has both types of quotation marks and needs to be updated in a SQL table where the column type is long string.

How can I convert it to proper SQL statement since if I use single or double quotation marks , it gives errors.

My SQL statement preparation in PHP is like this:

'update '.$table.' set value = '.$data.' where sectionId = '.$id;

I want to store the above html code in $data which is supposed to be a string in order to work. How can I do that?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found this PHP function

    mysqli_real_escape_string()

    It seems to do the job.


  2. 0

    Don’t do it.

    Use parametrized prepared statements like

    'update '.$table.' set value = ? where sectionId = ?';

    Here every ? is a parameter later passed to DBMS separately from query, and treated separately.

    As a result, you don’t need to escape anything (as you strings are not part of query anymore) and you are safe from SQL-injections.

    In your case I believe it would be something like:

    $stmt = $conn->prepare("update ".$table." set value = ? where sectionId = ?");
$stmt->bind_param("si", $data, $id);
$stmt->execute();

    Please notice, that table name couldn’t be substituted as parameter to query, so there concatenation is still used. Although I would recommend to consider replacing it with constant name to improve readability.

    Also, notice that first parameter of bind_param() takes string containing types of your parameters. Here I assumed your $data is string, and $id is integer, but you could need adjust to your data model.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search