0

I have read many similar questions mentioning this same error but wasn't able to apply the solutions mentioned to my case. I use a prepared statement to insert data in my database (MariaDB) using PHP and PDO. Most of the times it works, but at times it gives the following error:

PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'test <snip>

The error comes up when a special character (such as a single quote) is used in the data entered.

Here is the function used to enter the data in the DB:

function insert($table,$data){
            global $conn;
            ksort ($data);
            $fieldNames = '`'.implode('`, `', array_keys($data)).'`';
            $fieldvalues = "'".implode("','", array_values($data))."'";
            $sql = 'INSERT INTO '.$table.' ('.$fieldNames.') VALUES('.$fieldvalues.')';
            $statement = $this->conn->prepare($sql);
            $statement->execute(); 
          return 'true';        
        }

And here is an example of how the insert function is called:

  if (isset($_POST['name'])) {
                  $data = array(
                        'name'=>"any name",
                        'nickname'=>"any nickname",
                        'address'=>"any address",
                        );
        insert('table_name',$data);
    }

Any help on solving this issue would be appreciated.

edit: Here is is partial db/tabledump as per requested (edited for clarity):

-- MySQL dump 10.19  Distrib 10.3.29-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: database_name
-- ------------------------------------------------------
-- Server version   10.3.29-MariaDB-0+deb10u1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `sampletable`
--

DROP TABLE IF EXISTS `sampletable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sampletable` (
  `row_id` int(11) NOT NULL AUTO_INCREMENT,
  `data_id` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `data_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `data_address` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `data_name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=01 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
Esquirish
  • 185
  • 1
  • 12
  • can you add table structure/schema ? – Harsh Gundecha Aug 01 '21 at 04:31
  • if you are building the array as shown above, you've missed a `,` after the 2nd key/value pair i.e. after `'nickname'=>"any nickname"` , you also probably don't need `ksort` since key values are tied with each other and sql doesn't need the insertion column names to be in order of the columns defined in table. – Harsh Gundecha Aug 01 '21 at 04:37
  • @HarshGundecha i just added a table dump. The `,` missing was a mistake not present in the server code, i just corrected it. – Esquirish Aug 01 '21 at 04:50
  • I recommend using a prepared statement _properly_ with placeholders and bound variables. Your query is unstable/insecure (as you've discovered because there is no escaping done on the directly injected variables. Do NOT escape the values, just use the prepared statement as it is designed. https://phpdelusions.net/pdo_examples/insert – mickmackusa Aug 01 '21 at 04:58
  • @mickmackusa thanks, i have little/no experience with prepared statements. – Esquirish Aug 01 '21 at 05:06
  • 1
    Then the time to begin researching is now. This is not a skill that a developer can go without. – mickmackusa Aug 01 '21 at 05:06

0 Answers0