return one string with one ' in the text. I use this ' is another language is catalan the word is denominació d'origen.
there is a problem with sql and string. the problem is with the concatenation

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'origen'' at line 1 in /var/www/html/cacau/cacau_tipus_xocolata.php:36 Stack trace: #0 /var/www/html/cacau/cacau_tipus_xocolata.php(36): mysqli_query(Object(mysqli), 'SELECT xocolat...') #1 {main} thrown in /var/www/html/cacau/cacau_tipus_xocolata.php on line 36

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = mysqli_connect("localhost", "root", "P-11fl32fg14", "cacau");



    $query = "SELECT  DISTINCT tipus_xocolata.tipus_xocolata,tipus_xocolata.tipus_xocolata 
      FROM tipus_xocolata";
    $result = mysqli_query($mysqli, $query);
   
    /* numeric array */
    while ($row = mysqli_fetch_array($result, MYSQLI_BOTH))
    {     
         printf(
            '<a href="cacau_tipus_xocolata.php?tipus_xocolata=%s">%s</a><br>\n',
            urlencode($row['tipus_xocolata']),
            htmlentities(ucfirst($row['tipus_xocolata']))
        );
        <br> 
<?php      
        
    }
    
   
?>

    The error is because the value you are putting into the sql query statement contains an sql special character, e.g. the ', which is breaking the sql query syntax. This is also how sql injection is accomplished.

    The solution is to use a prepared query, with a ? place-holder in the sql query statement, then supply the value when the query gets executed. Unfortunately, the php mysqli database extension is overly complicated and inconsistent when dealing with prepared queries. This would be a good time to switch to the much simpler and more modern PDO database extension.

    As to the posted code/query. If the tipus_xocolata table is where the chocolate types are defined, there is only one row per chocolate type and the DINSTINCT keyword is not needed. Since this query only involves one table, there's no point in prepending the table name to each column. This is just unnecessary typing. Selecting the same column more than once doesn't do anything (you will only get the last occurrence of the value when fetching the data as an associative array) and isn't necessary, since you can reuse fetched column value as many times as you want. Also, this table should have an id column, which is an auto-increment primary index. You would use this id as the get parameter in any link, when storing related data, or performing a search within related data. lastly, I'm pretty sure it has been posted in more than one of the help forums you have been posting in, this not the intended way of using printf(). The place-holders/replacement-parameters in the overall-string being built should only be for the dynamic values. Edit: which I see you have now changed in the edited code.

      I put denominació d'origen with denominació d"origen in the database it works

      bertrc, in programing, that is called a kluge, i.e. you creating a special case to make something work, rather than fixing the actual problem.

      External data submitted to your web site can come from anywhere, not just your links/forms, can be anything, and cannot be trusted. The correct, fool-proof way of protecting against sql special characters in a value from being able to break the sql query syntax, for all data types, is to use a prepared query.

      The SQL error you have described looks nothing like the SQL query being constructed by your PHP here. Perhaps you have not identified the right PHP file generating that error?

      Also, you should probably avoid posting your database password in public.

        Write a Reply...