3

I'm trying to create a database and data source dynamically from the user input.

I'm not sure is that possible or not to create a database using cfquery tag without using data source.

For Data source creation

<cfscript>
    adminObj = createObject("component","cfide.adminapi.administrator");
    adminObj.login("admin password");  
    myObj = createObject("component","cfide.adminapi.datasource");
    myObj.setMSSQL(driver="MSSQLServer",
        name="datasourceName", host = "127.0.0.1", port = "1433",
        database = "database", username = "userName", password="password", login_timeout = "30",
        timeout = "20", interval = 7, buffer = "64000", blob_buffer = "64000", setStringParameterAsUnicode = "false",
        description = "", pooling = true, maxpooledstatements = 1000, enableMaxConnections = "true",
        maxConnections = "300", enable_clob = true, enable_blob = true, disable = false, storedProc = true,
        alter = false, grant = true, select = true, update = true, create = true, delete = true, drop = false,
        revoke = false
        );
</cfscript>

I've tried to create a data source using above code statically it creating Datasource successfully

For database creation

<cfquery name="createDB" result="res">
    create database #form.dbname#
</cfquery>

but not able to create a database using the above code. I got the error, my objective needs to create database then data source by using the user input string. enter image description here

Any advice or help is appreciable
Thanks in Advance.

Saravana Kumar
  • 168
  • 1
  • 9
  • Why? I _think_ that it is possible in a round-a-bout way, but ColdFusion server really shouldn't have that level of permissions to your database server. Are you using the `sa` account as your CF user connection, or do you have a specific connection for the CF server? – Shawn Feb 28 '20 at 11:32
  • 2
    It is a _really_ bad idea to let users create databases on your server. – Miguel-F Feb 28 '20 at 12:32
  • @shawn yes, I'm using sa account as a cf user connection – Saravana Kumar Feb 28 '20 at 12:41
  • @miguel-F yes, but this is scenario ☺ – Saravana Kumar Feb 28 '20 at 12:55
  • 3
    You need to use the datasource name in the that creates the database - though, I am not sure that will work. You may need to create the DB using a datasource that already exists and then create the new datasource. Also, I agree with everyone else...this is a bad idea...a very, very bad idea. – Scott Stroz Feb 28 '20 at 13:50
  • 2
    From what I remember, I think you can create another baseline database (like Master in SQL Server) and backup and restore that database. That gets you an actual datasource db to use. However, I'll reiterate again that I think this is a _very_ bad idea. – Shawn Feb 28 '20 at 14:37
  • 1
    I would also HIGHLY recommend that you create a special user for just CF to use and limit that account to what it's allowed to do. `sa` has _A LOT_ of privileges in your database. And you really don't want a web-accessible user to be able to do something like `DROP TABLE master` to your database. – Shawn Feb 28 '20 at 14:39
  • 1
    I'd also take a look at https://www.petefreitag.com/presentations/cfsummit/2016/cf2016-lockdown.pdf. Pete really knows his stuff, and this guide (and others for applicable versions) are extremely good. – Shawn Feb 28 '20 at 14:45
  • I suggest putting the code for creating the database into a stored procedure and granting the CF account permission to execute it. – Dan Bracuk Feb 28 '20 at 15:43
  • 1
    @DanBracuk _IF_ it really needs to be done. :-) – Shawn Feb 28 '20 at 16:08
  • 2
    One other note I'll add for future readers: One of the reasons this is a bad idea (despite the security aspects) is that you can open up a hole that allows for an easy denial-of-service, intentional or not. If this gets called multiple times, each of these database has a drive space cost. You can fill up the hard drives in your database server pretty quickly. Running out of drive space can be a tremendous headache. And even worse, if you are billed for the drive space you use, you can receive a fairly expensive surprise. – Shawn Feb 28 '20 at 17:03
  • 1
    You didn't mention why you want to grant this power to the user, so I'll just assume you are building an intranet application to maintain databases with a web frontend. If you need flexibility when it comes to database connections, you should disregard `cfquery`/datasources and instead [use the JDBC driver directly](https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15). `createObject("java", ...)` will be your tool. – Alex Feb 28 '20 at 21:50
  • @Shawn I've solved this scenario by using the master database. yes, that's bad idea but I think that has a solution. – Saravana Kumar Feb 29 '20 at 05:24
  • @Alex Just like CMS concepts. user/site admin need able to create their own database and table via UI (without knowledge of database and ColdFusion). – Saravana Kumar Feb 29 '20 at 05:56
  • `"...create their own database and table via UI (without knowledge of database and ColdFusion)"` <<< Yes, it _is_ possible, but there are plenty of things that can be done but shouldn't be done. What I'm saying is that you are opening yourself up to a **LOT** of problems if you allow this. – Shawn Feb 29 '20 at 20:47
  • Okay. I agree with you, will try to avoid this scenario :) – Saravana Kumar Mar 02 '20 at 09:48

1 Answers1

1

Can you convert your script to a function, and pass the desired database name from the form input as an argument?

runCreateDatabase = createDBforMe(databasename=form.dbname)

Richard_J_Nap
  • 11
  • 6