INSERT data problem!

[co.ador]

After having an string with apostrophes ', double quotes " or any other special characters, successfully escaped through one of themany php functions availables to escape, what other malfunctioning can be preventing to INSERT a string into the database?

[PFMaBiSmAd]

What error message does echoing mysql_error show when the INSERT query fails? There are probably a dozen possible reasons why an INSERT query could fail.

[co.ador]

it is not showing any error, i know I have to set up the mysql_error() function

 

<?php if {
INSERT
} 
else{
          // This is a major issue. NOT enough information was sent to log the item
          Error::LogError("Variable(s) Missing", "You must provide all of the information to log the rating of this item.");
        }?>

 

The INSERT is obviously not working otherwise it would be inserting, so else is set up to spit up the logError method which is not throwing it out don't know why.

 

It doesn't have the mysql_error() function in

 

Has any idea where can I put a function such as mysql_error () to be able to see what kind of error is preventing from doing x thing in the LogError method below if it is possible? thanks

 

<?php 

  public static function LogError($varTitle, $varDescription)
      {
        // Check Parameters
        if (strlen(trim($varTitle)) != 0 && strlen(trim($varDescription)) != 0)
        {
          array_push(self::$title, $varTitle);
          array_push(self::$type, "ERROR");
          array_push(self::$description, $varDescription);
          array_push(self::$datetime, date("m/d/Y H:i:s"));
          self::$numErrors++;
          
          return true;
        }
        
        return false;
      }
?>

 

 

       

[PFMaBiSmAd]

What database class are you using, because it is fairly likely that ALL the difficulty you are having is because the database class is not directly compatible with the suggested solutions and that you should be using class methods instead.

[co.ador]

Ok i understand

 

 

database class

<?php 
<?php header('Content-type: text/html; charset=utf-8');?>
<?php
  //////////////////////////////////////////////////////////////////////////////
  // Database Class 
  //============================================================================
  // Dependencies:
  //----------------------------------------------------------------------------
  // None
  //============================================================================
  // Modification History:
  //----------------------------------------------------------------------------
  // 2006-11-04: Created
  //////////////////////////////////////////////////////////////////////////////
  class Database extends Error
  {
    ## CONSTANT VARIABLES
      const DB_TYPES = 'mysql,mysqli'; // NO SPACES!
    ## END CONSTANT VARIABLES
    
    ## PUBLIC VARIABLES
    ## END PUBLIC VARIABLES
    
    ## PRIVATE VARIABLES
      private static $host;
      private static $port;
      private static $database;
      private static $username;
      private static $password;
      private static $type;
      private static $connection;
      private static $savedQueries;
      private static $savedResults;
    ## END PRIVATE VARIABLES
    
    ## CONSTRUCTOR
    ## END CONSTRUCTOR
    
    ## DECONSTRUCTOR
    ## END DECONSTRUCTOR
    
    ## PUBLIC METHODS
      // Initialize the Variables
      // Does not return anything, but acts like a constructor for Static classes
      public static function Initialize($varType, $varHost, $varPort, $varDatabase, $varUsername, $varPassword)
      {
        Error::Initialize();
        
        if (!self::ValidDatabaseTypes($varType))
        {
          Error::LogError("Database Type Invalid", "Database Type must be one of: " . self::DB_TYPES);
        }
        
        self::$host         = $varHost;
        self::$port         = $varPort;
        self::$type         = strtolower($varType);
        self::$database     = $varDatabase;
        self::$password     = $varPassword;
        self::$username     = $varUsername;
        self::$savedQueries = array();
        self::$savedResults = array();
        self::$connection   = self::ConnectToDatabase();
        
        self::SelectTheDatabase();
      }
      
      // DeInitialize the Variables
      // Does not return anything, but acts like a destructor for Static classes
      public static function DeInitialize()
      {
        // Remove Saved Queries
        for ($saved = 0; $saved < sizeof(self::$savedQueries); $saved++)
        {
          unset(self::$savedQueries[$saved]);
        }
        
        // Remove Saved Results
        for ($saved = 0; $saved < sizeof(self::$savedResults); $saved++)
        {
          unset(self::$savedResults[$saved]);
        }
        
        // Close the Database Connection
        switch (self::$type)
        {
          case "mysql":
            @mysql_close(self::$connection) or Error::LogError("MySQL Failed to Close", mysql_error(self::$connection));
            break;
          case "mysqli":
            @mysqli_close(self::$connection) or Error::LogError("MySQL Failed to Close", mysqli_error(self::$connection));
            break;
        }
        
        // Destroy Variables
        self::$host         = null;
        self::$port         = null;
        self::$type         = null;
        self::$database     = null;
        self::$password     = null;
        self::$username     = null;
        self::$connection   = null;
        self::$savedQueries = null;
        self::$savedResults = null;
        Error::DeInitialize();
      }
      
      // Database Types
      // Returns an array of database types
      public static function DatabaseTypes()
      {
        return split(",", self::DB_TYPES);
      }
      
      // Build Order By
      // Returns the SQL Syntax for ORDER BY
      public static function BuildOrderBy($varColumnName, $varDirection)
      {
        $orderby = "";
        if (self::$connection)
        {
          switch (self::$type)
          {
            case "mysql":
            case "mysqli":
              $orderby = "ORDER BY `{$varColumnName}` {$varDirection}";
              break;
          }
        }
        
        return $orderby;
      }
      
      // Build Limit
      // Returns the SQL Syntax for LIMIT
      public static function BuildLimit($varStartingRow, $varNumberOfRows)
      {
        $limit = "";
        if (self::$connection)
        {
          switch (self::$type)
          {
            case "mysql":
            case "mysqli":
              $limit = "LIMIT {$varStartingRow}, {$varNumberOfRows}";
              break;
          }
        }
        
        return $limit;
      }
      
      // Execute SQL Query
      // Returns the result of the query, which is typically a resource id
      public static function ExecuteQuery($sql, $name)
      {
        if (self::$connection)
        {
          if (strlen(trim($name)) != 0)
          {
            switch (self::$type)
            {
              case "mysql":
                if (!array_key_exists($name, self::$savedQueries))
                {
                  self::$savedQueries[$name] = @mysql_query($sql, self::$connection) or Error::LogError("Query Failed", mysql_error(self::$connection));
                }
                break;
              case "mysqli":
                if (!array_key_exists($name, self::$savedQueries))
                {
                  self::$savedQueries[$name] = @mysqli_query(self::$connection, $sql) or Error::LogError("Query Failed", mysqli_error(self::$connection));
                }
                break;
            }
            
            return self::$savedQueries[$name];
          }
          else
          {
            Error::LogError("Execute Query Name Missing", "The name parameter was empty, please provide a name for the query.");
          }
        }
        
        return null;
      }
      
      // Fetch Results
      // Returns an array of the query results
      public static function FetchResults($name)
      {
        $results = array();
        if (self::$connection)
        {
          if (strlen(trim($name)) != 0 && (array_key_exists($name, self::$savedQueries) || array_key_exists($name, self::$savedResults)))
          {
            if (array_key_exists($name, self::$savedQueries))
            {
              switch (self::$type)
              {
                case "mysql":
                  $row = 0;
                  while ($currentResult = @mysql_fetch_assoc(self::$savedQueries[$name]))
                  {
                    $col = 0;
                    foreach ($currentResult as $key => $value)
                    {
                      $results[$row][$col] = $value;
                      $results[$row][$key] = $value;
                      $col++;
                    }
                    
                    $row++;
                  }
                  break;
                case "mysqli":
                  $row = 0;
                  while ($currentResult = @mysqli_fetch_assoc(self::$savedQueries[$name]))
                  {
                    $col = 0;
                    foreach ($currentResult as $key => $value)
                    {
                      $results[$row][$col] = $value;
                      $results[$row][$key] = $value;
                      $col++;
                    }
                    
                    $row++;
                  }
                  break;
              }
            
              self::$savedResults[$name] = $results;
            }
            else
            {
              $results = self::$savedResults[$name];
            }
          }
          else
          {
            if (strlen(trim($name)) == 0)
            {
              Error::LogError("Fetch Results Name Missing", "The name parameter was empty, the name is required so it knows which results to return.");
            }
            else
            {
              Error::LogError("Fetch Results Name ('{$name}') Not Found", "The name provided did not have any query results associated with it.");
            }
          }
        }
        
        return $results;
      }
      
      // Free SQL Query Results
      // Returns nothing
      public static function FreeResults($name)
      {
        if (self::$connection)
        {
          if (strlen(trim($name)) != 0 && array_key_exists($name, self::$savedQueries))
          {
            switch (self::$type)
            {
              case "mysql":
                @mysql_free_result(self::$savedQueries[$name]) or Error::LogError("Free Results Error", mysql_error(self::$connection));
                unset(self::$savedQueries[$name]);
                break;
              case "mysqli":
                @mysqli_free_result(self::$savedQueries[$name]) or Error::LogError("Free Results Error", mysqli_error(self::$connection));
                unset(self::$savedQueries[$name]);
                break;
            }
          }
          else
          {
            if (strlen(trim($name)) == 0)
            {
              Error::LogError("Free Results Name Missing", "The name parameter was empty, the name is required so it knows which results to free up from memory.");
            }
            else
            {
              Error::LogWarning("Free Results Name ('{$name}') Not Found", "The name provided did not have any query results associated with it.");
            }
          }
        }
      }
      
      // Remove Saved Results
      // Returns nothing
      public static function RemoveSavedResults($name)
      {
        if (strlen(trim($name)) != 0 && array_key_exists($name, self::$savedResults))
        {
          unset(self::$savedResults[$name]);
        }
        else
        {
          if (strlen(trim($name)) == 0)
          {
            Error::LogError("Remove Saved Result Name Missing", "The name parameter was empty, the name is required so it knows which query to remove.");
          }
          else
          {
            Error::LogWarning("Remove Saved Result Name ('{$name}') Not Found", "The name provided was not a saved query.");
          }
        }
      }
      
      // Attempt Connect To Database
      // Returns true or false depending on if the connection failed or succeeded
      public static function AttemptConnectToDatabase($varType, $varHost, $varPort, $varDatabase, $varUsername, $varPassword)
      {
        self::$type       = $varType;
        self::$host       = $varHost;
        self::$port       = $varPort;
        self::$database   = $varDatabase;
        self::$username   = $varUsername;
        self::$password   = $varPassword;
        
        Error::ClearErrors();
        self::$connection = self::ConnectToDatabase();
        
        if (!Error::HasErrors())
        {
          return true;
        }
        else
        {
          return false;
        }
      }
      
      // MySQL Version
      // Returns the mysql version number
      public static function MysqlVersion()
      {
        $version = "";
        if (self::$connection)
        {
          switch (self::$type)
          {
            case "mysql":
              $version = mysql_get_server_info(self::$connection);
              break;
            case "mysqli":
              $version = mysqli_get_server_info(self::$connection);
              break;
          }
        }
        
        return $version;
      }
    ## END PUBLIC METHODS
    
    ## PRIVATE METHODS
      // Connect to Database
      // Returns the database connection resource
      private static function ConnectToDatabase()
      {
        $link = null;
        
        switch (self::$type)
        {
          case "mysql":
            if (strlen(trim(self::$port)) != 0)
            {
              $link = mysql_connect(self::$host . ":" . self::$port, self::$username, self::$password) or Error::LogError("Database Error", mysql_error());
            }
            else
            {
              $link = mysql_connect(self::$host, self::$username, self::$password) or Error::LogError("Database Error", mysql_error());
            }
            break;
          case "mysqli":
            $link = mysqli_connect(self::$host, self::$username, self::$password, self::$database, self::$port) or Error::LogError("Database Error", mysqli_connect_error());
            break;
        }
        
        return $link;
      }
      
      // Select the Database
      // Returns nothing
      private static function SelectTheDatabase()
      {
        switch (self::$type)
        {
          case "mysql":
	    mysql_set_charset('utf8', self::$connection);
            @mysql_select_db(self::$database, self::$connection) or Error::LogError("Database Selection", mysql_error(self::$connection));
            break;
        }
      }
      
      // Valid Database Types
      // Returns true or false depending on if the database type is valid
      private static function ValidDatabaseTypes($varType)
      {
        $types = split(',', str_replace(" ", "", self::DB_TYPES));
        
        return in_array($varType, $types);
      }
    ## END PRIVATE METHODS
    
    ## PROTECTED METHODS
    ## END PROTECTED METHODS
  }
?>
?>

[ChemicalBliss]

Hmm, looks like an old outdated and incomplete class imo.

 

There are many advanced and up-to-date classes out there but if you want to use this class, try a few debugging steps:

 

Execute your query manually., Execute it in phpmyadmin, or remove the silence symbols (forgot the name) in front of the mysql_query calls in the class:

 

<?php

              case "mysql":
                if (!array_key_exists($name, self::$savedQueries))
                {
                  self::$savedQueries[$name] = mysql_query($sql, self::$connection) or Error::LogError("Query Failed", mysql_error(self::$connection));
                }
                break;
              case "mysqli":
                if (!array_key_exists($name, self::$savedQueries))
                {
                  self::$savedQueries[$name] = mysqli_query(self::$connection, $sql) or Error::LogError("Query Failed", mysqli_error(self::$connection));
                }
                break;

?>

 

Hope this helps,

-CB-

[co.ador]

I have execute the query manually in phpmyadmin just as chemicalbliss has suggested and it through an syntax error

 

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 '"INSERT INTO `rating` (`item_name`, `rating`, `ip_address`, `date_rated`) VALUES' at line 1

 

Around the VALUES cluase...

[ChemicalBliss]

Looks like you have a double-quote beofre the query.

 

When mysql gives a syntax error, it shows from the beginning of the error. so the error lies _just_ before the INSERT clause.

 

Try echoing the query into the browser and seeing if it is what you expected. There is ofc a problem with your query, and probably not the class your using. Its the data your passing to the class (your query).

 

-CB-

[co.ador]

How can I echo this query ExecuteQuery method?

 

<?php 
Database::ExecuteQuery("INSERT INTO `rating` (`item_name`, `rating`, `ip_address`, `date_rated`) VALUES ('{$varItem}', {$varRating}, '{$ipAddress}', NOW())", "InsertRating");

            Database::FetchResults("InsertRating");
            Database::FreeResults("InsertRating");
            Database::RemoveSavedResults("InsertRating");
?>

 

ExecuteQuery method referred in the query above.

<?php
public static function ExecuteQuery($sql, $name)
     
  {
        if (self::$connection)
        {
          if (strlen(trim($name)) != 0)
          {
            switch (self::$type)
            {
              case "mysql":
                if (!array_key_exists($name, self::$savedQueries))
                {
                  self::$savedQueries[$name] = @mysql_query($sql, self::$connection) or Error::LogError("Query Failed", mysql_error(self::$connection));
                }
                break;
              case "mysqli":
                if (!array_key_exists($name, self::$savedQueries))
                {
                  self::$savedQueries[$name] = @mysqli_query(self::$connection, $sql) or Error::LogError("Query Failed", mysqli_error(self::$connection));
                }
                break;
            }
            
            return self::$savedQueries[$name];
          }
          else
          {
            Error::LogError("Execute Query Name Missing", "The name parameter was empty, please provide a name for the query.");
          }
        }
        
        return null;
      }
?>

 

you can see in the link below how the 4th and 6th iteration is being escape but still it won't INSERT iteration where it's query string has an apostrophe in.

 

at the link you will see I have echoed the string to proof it is escaped but still it won't INSERT.

 

http://www.nyhungry.com/indexpagination.php?currentpage=2&strZipCode=10468

[ChemicalBliss]

Hmm, the query looks sound.

 

You would just store the query in a variable, before using it in executequery. Then you can do whatever you want with the query before using it .

 

Hmm, what method of sanitization are you using? i would reccommend using mysql_real_escape_string($variable_here); on all the values, eg:

 

$varItem = mysql_real_escape_string($varItem);

 

-CB-