Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, and XML. Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, and XML. THE WORLD'S LARGEST WEB DEVELOPER SITE. HTML CSS JAVASCRIPT SQL PHP BOOTSTRAP HOW TO JQUERY W3.CSS PYTHON XML MORE. PHP Create MySQL Tables.

1. Mysql Php Script Generator For Sqlite
2. Php Mysql Example

I do not think that this has been posted before - as this is a very specific problem.

I have a script that generates a 'create table' script with a custom number of columns with custom types and names.

Here is a sample that should give you enough to work from -

But for some reason, when I run this query, it returns a syntax error in MySQL. This is probably to do with line breaks, but I can't figure it out. HELP!

1 Answer

You have multiple SQL-injection holes
mysql_real_escape_string() only works for values, not for anything else.
Also you are using it wrong, you need to quote your values aka parameters in single quotes.

If you don't mysql_real_escape_string() will not work and you will get syntax errors as a bonus.
In a CREATE statement there are no parameters, so escaping makes no sense and serves no purpose.

You need to whitelist your column names because this code does absolutely nothing to protect you.

Coding horror

see this question for answers:
How to prevent SQL injection with dynamic tablenames?

Download drivers, software, firmware and manuals for your Canon product and get access to online technical support resources and troubleshooting. FAX-L350 - Support - Download drivers, software and manuals - Canon UK. For certain products, a driver is necessary to enable the connection between your product and a computer. On this tab, you will find the applicable drivers for your product or in the absence of any drivers, an explanation of your product's compatibility with each operating system. Canon FAX-L350 drivers will help to eliminate failures and correct errors in your device's operation. Download Canon FAX-L350 drivers for different OS Windows versions (32 and 64 bit). After you have downloaded the archive with Canon FAX-L350 driver, unpack the file in any folder and run it. Free drivers for Canon FAX-L350. Found 17 files for Windows XP, Windows 2000, Windows 98, Windows 95, Windows NT. Select driver to download. Update the Canon FAX-L350 Fax Drivers For Windows 7 with ease. Easy Driver Pro makes getting the Official Canon FAX-L350 Fax Drivers For Windows 7 a snap. Easy Driver Pro will scan your computer for missing, corrupt, and outdated Drivers. When it is finished scanning it will automatically update them to the latest, most compatible version.

Never use n in a query
Use separate the elements using spaces. MySQL is perfectly happy to accept your query as one long string.
If you want to pretty-print your query, use two spaces in place of n and replace a double space by a linebreak in the code that displays the query on the screen.

More SQL-injection
$SESSION['user_id'] is not secure, you suggest you convert that into an integer and then feed it into the query. Because you cannot check it against a whitelist and escaping tablenames is pointless.

Surround all table and column names in backticks `
This is not needed for handwritten code, but for autogenerated code it is essential.

Example:

Learn from the master
You can generate the create statement of a table in MySQL using the following MySQL query:

Your code needs to replicate the output of this statement exactly.

Not the answer you're looking for? Browse other questions tagged phpmysqlsqldatabasecode-generation or ask your own question.

I'm programming a script using PHP and MySQL and I want to get a unique id (consisting of a string: capitals and small letters with numbers) like: gHYtUUi5b.I found many functions in PHP that can generate such numbers but I'm afraid about how to ensure the id is unique!

UPDATE: uuid is long, I mean such id like: (P5Dc) an 11 alphanumeric char.

14 Answers

A programmatic way can be to:

• add a UNIQUE INDEX to the field
• generate a random string in PHP
• loop in PHP ( while( ! DO_THE_INSERT ) )
  • generate another string

Note:

• This can be dirty, but has the advantage to be DBMS-agnostic
• Even if you choose to use a DBMS specific unique ID generator function (UUID, etc)it is a best practice to assure the field HAS to be UNIQUE, using the index
• the loop is statistically not executed at all, it is entered only on insert failure

You may like the way that we do it. I wanted a reversible unique code that looked 'random' -a fairly common problem.

• We take an input number such as 1,942.
• Left pad it into a string: '0000001942'
• Put the last two digits onto the front: '4200000019'
• Convert that into a number: 4,200,000,019

We now have a number that varies wildly between calls and is guaranteed to be less than 10,000,000,000. Not a bad start.

• Convert that number to a Base 34 string: '2oevc0b'
• Replace any zeros with 'y' and any ones with 'z': '2oevcyb'
• Upshift: '2OEVCYB'

The reason for choosing base 34 is so that we don't worry about 0/O and 1/l collisions. Now you have a short random-looking key that you can use to look up a LONG database identifier.

How you generate the unique_ids is a useful question - but you seem to be making a counter productive assumption about when you generate them!

My point is that you do not need to generate these unique id's at the time of creating your rows, because they are essentially independent of the data being inserted.

What I do is pre-generate unique id's for future use, that way I can take my own sweet time and absolutely guarantee they are unique, and there's no processing to be done at the time of the insert.

For example I have an orders table with order_id in it. This id is generated on the fly when the user enters the order, incrementally 1,2,3 etc forever. The user does not need to see this internal id.

Then I have another table - unique_ids with (order_id, unique_id). I have a routine that runs every night which pre-loads this table with enough unique_id rows to more than cover the orders that might be inserted in the next 24 hours. (If I ever get 10000 orders in one day I'll have a problem - but that would be a good problem to have!)

This approach guarantees uniqueness and takes any processing load away from the insert transaction and into the batch routine, where it does not affect the user.

If you use MySQL with version higher than 5.7.4, you can use the newly added RANDOM_BYTES function:

This will result in a random string such as GgwEvafNLWQ3+ockEST00A.

Use UUID function.

I don't know the source of your procedures in PHP that generates unique values. If it is library function they should guarantee that your value is really unique. Check in documentation. You should, hovewer, use this function all the time. If you, for example, use PHP function to generate unique value, and then you decide to use MySQL function, you can generate value that already exist. In this case putting UNIQUE INDEX on the column is also a good idea.

And call the stored procedure as GenerateUniqueValue('tableName','columnName'). This will give you a 8 digit unique character everytime.

For uniqueness what I do is I take the Unix timestamp and append a random string to it and use that.

Below is just for reference of numeric unique random id..

Mysql Php Script Generator For Sqlite

it may help you..

and you can add char with the code like ->$rand=mt_rand(10000,999999) .$randomchar; // assume $radomchar contains char;

You might also consider using crypt()* to generate a [nearly-guaranteed] unique ID inside your contraints.

crypt() as suggested and store salt in some configuration file, Start salt from 1 and if you find duplicate move to next value 2. You can use 2 chars, but that will give you enough combination for salt.

You can generate string from openssl_random_pseudo_bytes(8). So this should give random and short string (11 char) when run with crypt().

Remove salt from result and there will be only 11 chars that should be enough random for 100+ millions if you change salt on every fail of random.

To get unique and random looking tokens you could just encrypt your primary key i.e.:

SELECT HEX(AES_ENCRYPT(your_pk,'your_password')) AS 'token' FROM your_table;

This is good enough plus its reversable so you'd not have to store that token in your table but to generate it instead.

Another advantage is once you decode your PK from that token you do not have to do heavy full text searches over your table but simple and quick PK search.

Theres one small problem though. MySql supports different block encryption modes which if changed will completely change your token space making old tokens useless..

To overcome this one could set that variable before token generated i.e.:

SET block_encryption_mode = 'aes-256-cbc';

However that a bit waste.. The solution for this is to attach an encryption mode used marker to the token:

Another problem may come up if you wish to persist that token in your table on INSERT because to generate it you need to know primary_key for the record which was not inserted yet.. Ofcourse you might just INSERT and then UPDATE with LAST_INSERT_ID() but again - theres a better solution:

One last but not least advantage of this solution is you can easily replicate it in php, python, js or any other language you might use.

Php Mysql Example

Not the answer you're looking for? Browse other questions tagged phpsqlmysql or ask your own question.