Error code 42000 sqlstate 42000 - Исправление ошибок и поиск оптимальных решений проблем

I was looking for answer for last 3 hours, and i don’t know what to do. Here is the code:

    function get_data($tablename)
    {
        try
        {
            $conn = $this->conn();
            $stmt = $conn->prepare("SELECT * FROM :tablename ORDER BY id");
            $stmt->bindParam(':tablename', $tablename, PDO::PARAM_STR);
            $stmt->execute();
            return $stmt;
        }
        catch (Exception $e)
        {
            echo "ERROR:" . $e->getMessage();
        }
    }

And here is the error:

ERROR:SQLSTATE[42000]: Syntax error or access violation: 1064 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 »products’ ORDER BY id’ at line 1

What I’ve done wrong?…

marc_s

722k173 gold badges1320 silver badges1443 bronze badges

asked Jun 8, 2013 at 8:44

As noted here (thanks @YourCommonSense), you can’t use a parameter as a table name; and if you do, one of two things will happen:

With proper prepared statements, the prepared-statement module will throw an exception (and quite rightly so, as you’ve asked it to do the impossible).
With emulated prepared statements, the parameter will be blindly escaped, single-quoted, and substituted in, causing an SQL syntax error. This is what’s happened here.

That’s the problem. As for solutions:

Reevaluate your database design. Do you really need to split data across different tables like that? If not, combine the relevant data into a single table, and query accordingly.
If you’re happy with the design (or can’t change it), you’ll need an ugly insecure hack like the following:
```
function get_data($tablename, $acceptable_tablenames = array()) {
  /* $acceptable_tablenames is an array of strings, containing
   *  table names that you'll accept. It's your job to make sure
   *  these are safe; this is a much easier task than arbitrary
   *  sanitization.
   */
  if (array_search($tablename, $acceptable_tablenames, true) === FALSE) {
    throw new Exception("Invalid table name"); /* Improve me! */
  } else {
    /* your try/catch block with PDO stuff in it goes here
     * make sure to actually return the data
     */
  }
}
```
Call it as get_data($table, array("my_datatable_1", "my_datatable_2")). Credit to the post linked to at the start of my answer for inspiration.

answered Jun 8, 2013 at 9:25

PDO escapes parameters with single quotes ('). MySql table names need to be escaped with backticks (`).

In the example provided the tablename parameter, Products, is being escaped single quotes. This is a safety feature of the PDO engine to prevent injection attacks.

Assuming that value of $tablename makes sense in applications context (eg. validating that the current user can see all the data in the specified table.).

The following would work:

function get_data($tablename)
{
    try
    {
        $conn = $this->conn();
        $stmt = $conn->prepare("SELECT * FROM `" . str_replace("`","``",$tablename) . "` ORDER BY id;");
        $stmt->execute();
        return $stmt;
    }
    catch (Exception $e)
    {
        echo "ERROR:" . $e->getMessage();
    }
}

answered Jun 8, 2013 at 8:52

squirlysquirly

7435 silver badges13 bronze badges

Источник

“sqlstate42000 syntax error or access violation” error occurs due to any syntax error or extra space or no space in the user’s SQL query.

We’ve seen many of our customers come across this error while running a script after they have restored a database.

However, the best part is that if there is any syntax error, it specifies the line number to identify the exact location of the error easily. Syntax error in the SQL query may include improper entries of extra space or no space, etc.

Here at Bobcares, we have seen several such SQL related errors as part of our Server Management Services for web hosts and online service providers.

Today we’ll take a look at how to fix this SQL error.

How we fix ‘sqlstate42000 syntax error or access violation’

Now, let’s see the major reasons and how our Support Engineers fix this error by correcting the syntax problems.

Many customers approached us with the error, ‘sqlstate[42000] syntax error or access violation’.

On detailed analysis, we could trace that in most cases the error happens due to the wrong syntax entry.

Some common syntax mistakes we investigated are listed as following.

Missing parenthesis

One of our customers approached us with the error ‘sqlstate[42000] syntax error or access violation’. On further analysis with the code, we traced that, the problem arose due to missing parenthesis.

For instance, the code is as follows.

B::query(Database::SELECT, 'SELECT COUNT(*) as `count`,`region`, MONTHNAME(`date`) as`month`
FROM tempur_stores.stats
WHERE `date` > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK)
AND `date` < DATE(NOW())
GROUP BY `region`, MONTH(`date`');

Here, in the last line, we could see that a missing parenthesis ‘)’. We then Just put a parenthesis ) before that apostrophe(after ‘date’) and the error got fixed.

Missing backticks (`)

Another customer was facing the same error after adding details to the database.

After going through the code we traced that in the syntax there were missing backticks at relevant places.

For instance, a small part of the code is as follows.

$query1 = "INSERT INTO order (order_details, order_address, customer_id, customer_name, delivery_type, paid) VALUES(:details,:address,:d,:name,:delivery,:paid);";
$sql=$conn->prepare($query1);
$sql->bindParam(':details', $details);

Here, the order is a reserved keyword. We have to add backticks ` around it to use it. After adding the backticks, the code worked properly.

Extra space or no space

Additionally, in some cases, we could see extra space or no space added in syntax which resulted in this error. On removing such entries the error ‘sqlstate[42000] syntax error or access violation’ got resolved.

[Still, having the problem with ‘sqlstate42000 syntax error or access violation’?- We’re available to help you]

Conclusion

In short, ‘sqlstate42000 syntax error or access violation’ occurs mainly due to wrong syntax entry or extra space or no space in the user’s SQL query. Today, we saw how our Support Engineers help the customers to resolve this error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

If you are working with MySQL 5.7 and you find an exception when storing a lot of fields with text format:

SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

And you are sure that the row format of the table is set to Dynamic, you are probably facing an issue with the innodb log file size and the strict mode. In this article, we’ll show you how to prevent this exception from appearing in MySQL 5.7.

1. Find my.cnf file

As first step, you will need to search for the configuration file of MySQL. There is no internal MySQL command to trace the location of this file, so the file might be in 5 (or more) locations, and they would all be valid because they load cascading:

/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
[datadir]/my.cnf
~/.my.cnf

Those are the default locations MySQL looks at, however if you still don’t find the correct file, you may run the following command on your terminal:

find / -name my.cnf

Once you find the file, open it with a CLI editor like nano and follow the next step.

2. Increase innodb_log_file_size value

In our case, the file is located at /etc/mysql/my.cnf, so we could edit the file with nano using the file with the following command:

nano /etc/mysql/my.cnf

You will need to disable the strict mode of MySQL and increase the size of innodb log file. When innodb_strict_mode is enabled, InnoDB returns errors rather than warnings for certain conditions. Like many database management systems, MySQL uses logs to achieve data durability (when using the default InnoDB storage engine). This ensures that when a transaction is committed, data is not lost in the event of crash or power loss. MySQL’s InnoDB storage engine uses a fixed size (circular) Redo log space. The size is controlled by innodb_log_file_size. If you increase the value of this property, you will get rid off this exception when storing multiple columns of text in MySQL 5.7.

The theme about which size is right for the innodb log file won’t be covered in this article, instead we recommend you to read this article that contains a detailed explanation and facts about how to choose this value. As we are just sharing with you the solution to this problem, we’ll use the value of 512M, so the parameters to add to the mysqld block of the my.cfn file will be:

# Important: inside the mysqld block
[mysqld]
# Add new log file size
innodb_log_file_size=512M
# Disable strict mode
innodb_strict_mode=0

An example of how the file should look like:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
# Add new log file size
innodb_log_file_size=512M
# Disable strict mode
innodb_strict_mode=0

Although we didn’t cover a detailed explanation of the problem caused by the row size limitation that is removed by the dynamic row format, you may want to inform yourself about this problem visiting this article. After saving changes in the file , restart mysql with the cli depending of your os and installation process e.g:

# Ubuntu
sudo service mysql restart

# CentOS
/etc/init.d/mysqld start

Happy coding !

Источник

Remove From My Forums

Question
Hello eveyone

I am using sql server 2008 r2 maintainance plan. I take the weekly backup of (multaqaSSDB) database

The job was succeeded….But in sql server agent (log file viewer) I get the message

Message
[298] SQLServer Error: 4060, Cannot open database «MultaqaSSPDB1» requested by the login. The login failed. [SQLSTATE 42000]

but I don’t have (MultaqaSSPDB1)this database in instance……

—————

please help me how can I solve this issue

Note: There is two job related to this database:

1. MultaqaSSPDB_Job_DeleteExpiredSessions

2. MultaqaSSPDB1_Job_DeleteExpiredSessions

please need your responce

Thanks

samiya

Answers

Maybe in the Maintenance Plan you have specified the databases, and you later deleted the database. Did you ever had MultaqaSSPDB1 database in your server?

Open the Maintenance Plan and check which databases are selected for the each task. There should be MultaqaSSPDB1 in them.
- Edited by
  
  Wednesday, July 4, 2012 2:03 PM
- Proposed as answer by
  Ramesh Babu Vavilla
  Wednesday, July 4, 2012 3:51 PM
- Marked as answer by
  Ed Price — MSFTMicrosoft employee
  Monday, December 31, 2012 7:23 AM

Источник

How we fix ‘sqlstate42000 syntax error or access violation’

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

1. Find my.cnf file

2. Increase innodb_log_file_size value

Question

Answers