Can not create database jdbc [duplicate]

Question

I'm trying to create a database with java jdbc with a method so i'm passing the name type string of database as argument to database but i'm facing an issue which is 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 ''Algebra'' at line 1

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DbTest {
private Connection connection;
public void createDb(String name) throws SQLException {
    connection = DriverManager.getConnection
            ("jdbc:mysql://localhost/?user=root&password=root");

    String createDbSql = "CREATE DATABASE IF NOT EXISTS ?";
    PreparedStatement createDbStat = connection.prepareStatement(createDbSql);
    createDbStat.setString(1,name);
    createDbStat.executeUpdate();
}

DbTest() {
    try {
        createDb("Algebra");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void main(String[] args) {
    new DbTest();
}
}

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

When you use createDbStat.setString(1, name); it will create a query like this :

CREATE DATABASE IF NOT EXISTS 'databasename'
//----------------------------^____________^

And this is a wrong syntax, the correct should be :

CREATE DATABASE IF NOT EXISTS databasename

to solve your problem you can just use :

String createDbSql = String.format("CREATE DATABASE IF NOT EXISTS `%s`", name);
//                                                                ^^^^
PreparedStatement createDbStat = connection.prepareStatement(createDbSql);
//createDbStat.setString(1,name); no need for this
createDbStat.executeUpdate();

For security reason

Just for security reason, and to avoid SQL Injection make sure that your database name match this:

if(name.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")){
   //Correct name
}

for more details read this Check for valid SQL column name

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 13, 2018 at 20:54

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniele · Accepted Answer · 2018-05-13 21:21:36Z

1

You can't bind your parameter (1) to the database name- you'll have to use string concatenation in this case.

Your question is also similar to

How to use a tablename variable for a java prepared statement insert

and

CREATE DATABASE query using java jdbc and prepared statement returns syntax error

answered May 13, 2018 at 21:21

Daniele

2,8471 gold badge17 silver badges21 bronze badges

Collectives™ on Stack Overflow

Can not create database jdbc [duplicate]

2 Answers 2

For security reason

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

For security reason

Comments

Comments

Linked

Related